.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

How to write a select to return all metadata

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

Hi;

We need to pull a lot of metadata from the database. Specifically we need to get (on Sql Server 2000, 2005, & 2008):

  1. All tables & views - their schema, name, description, and if they are a system or user object.
  2. All table/view columns - their name, description, data type, and, if it is a foreign key, the table.column of the primary key it links to.
  3. All stored procedures - their name, description, and return type, and if they are a system or user object.
  4. All procedure parameters - their name, description, data type, data length, default (if any), and direction.

We have selects to do this but they are very complex and slow. We recently had an Oracle systems engineer help us on this same topic on Oracle and he had us create a temporary table that had all this info, we then read the rows in the table to get all this info and then dropped the table. It was a gigantic performance improvement.

So... Any suggestions on the best way to pull out this info? We grab it all at once so a temporary table is fine. Individual selects for each step is fine too. Suggestions on how best to do this are very appreciated.

thanks - dave


Very funny video - Reporting as a Metaphor


View Complete Post


More Related Resource Links

How to write a select to return all metadata

  

Hi;

We need to pull a lot of metadata from the database. Specifically we need to get (on Sql Server 2000, 2005, & 2008):

  1. All tables & views - their schema, name, description, and if they are a system or user object.
  2. All table/view columns - their name, description, data type, and, if it is a foreign key, the table.column of the primary key it links to.
  3. All stored procedures - their name, description, and return type, and if they are a system or user object.
  4. All procedure parameters - their name, description, data type, data length, default (if any), and direction.

We have selects to do this but they are very complex and slow. We recently had an Oracle systems engineer help us on this same topic on Oracle and he had us create a temporary table that had all this info, we then read the rows in the table to get all this info and then dropped the table. It was a gigantic performance improvement.

So... Any suggestions on the best way to pull out this info? We grab it all at once so a temporary table is fine. Individual selects for each step is fine too. Suggestions on how best to do this are very appreciated.

thanks - dave


Very funny video - Reporting as a Metaphor

SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Write select query with a paremetrized where clause, and populate database with the retrieved column

  
I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Error: "Select statements included within a function cannot return data to a client"

  
hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

I have the following 4 select statements.. but the 4th one doesnt return what i think i should be ge

  

I have a report that is presented to the client. It consists of "Scheduled", "Uncompleted", "Completed" and "All". Now what I PERSONALLY think these reports should return for the first 3 should total what is presented in the "All" report.

if i run the report "Scheduled" it returns 0
if i run the report "Uncompleted" it returns 157
if i run the report "Completed" it returns 59
But when i run the "All" report it returns 210

Which leaves a difference of 6. Can anyone tell me what im doing wrong with the following 4 select statements?

--------------------------------
Here is the scheduled report:
--------------------------------
select p.permit_num,K.task_id,K.task_due_date,K.date_completed,K.complete_ind,K.inspRpt_ind,C.*, R.service_rep_name,L.*,S.sys_name,M.manu_name, SI.sentAgc_date 
from tasks K, service_contracts C,service_reps R,locations L, manufacturers M, sys_inspection SI, systems S left outer join sys_permits sp on sp.sys_id = s.sys_id left outer join permits p on p.permit_id = sp.permit_id and p.active = 1 
where K.service_contract_id=C.service_contract_id and C.sys_id=S.sys_id and C.service_rep_id=R.service_rep_id and M.manu_id=S.manu_id and S.location_id=L.location_id and

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

  
This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is

Select Sql does not return the data eventhough the data does in particular table.

  

Hi Dude,

I am new to Sql server 2005. I have a table in which contains many data. I need to take particular data. For example, I want to take value of 110652.795813712 from FTEBASEPAY column .  So i have wrote the sql statment like in below.

SELECT * From tblEmployees where FTEBASEPAY='53842.7782655718'

But i am not able to get the particular value. Manually i have seen the tblEmployees table, in which contains the particular data ('53842.7782655718').

When i execute the above select statement, there is no result for it. Please let me know anyone face the same problem? Please give a solution for it. What i have to for overcome this issue?I have to give one more information, the FTEBASEPAY dataType is float in tblEmployees table.

Thanks in Advance

 


Stored Procedure Metadata failure- Dummy select not working

  

I have a procedure that involves creation of temp table, inserting data into it and use this data to populate another table variable whose output forms the dataset for the OLE DB Source.

Here is the general outlay of the procedure (just a sample data) -

CREATE PROCEDURE GetSettlementData
AS
BEGIN
SET NOCOUNT ON
SET FMTONLY OFF

IF 1=0
BEGIN
SELECT 
		CAST(NULL AS INT) AS ID
		,CAST(NULL AS INT) AS Marks
END

CREATE TABLE #Tmp_GetData
(
	ID INT,
	MARKS 

Losing my Memory About Using a Select to Write a Selct Script

  

Folks,

I'm trying to write a select script which will grant user perms on all user tables in a database & I'm having a tough time remembering the syntax. I do believe it's something like...

Select

 

'GRANT All ON OBJECT::'+[name] from sysobjects where xtype =

select query doesnt return values

  

hi all, i need your assistance please.

 

select Comments.Id,SuroundId,ArticleId,PosterId,PostRime,Subject,Body,Visible,Users.Username
from dbo.Comments
inner join Users on Comments.PosterId=Users.Id
where SuroundId=@suround and ArticleId=@articleId


for some reason it doesnt return any values.

before i've added the inner join it worked perfectly.

i couldn't find my mistake, though i passed over it several times.

 

thanks. 


How can I get Resolver.FindCompletions, in the R2 SMO release, to return all metadata from a SqlServ

  

I've been using the advice from a previous thread on Resolver.FindCompletions (http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/a0800f09-4610-4890-af85-15cc7e49ac46) and for SqlServer 2k8, it works great. However, when connected to a SqlServer2k8 R2 instance, I'm seeing a problem where the completion list that's returned is missing items that I get when connected to plain 'ol Sql 2k8. The most glaring omission is tables; the completion lists for Sql 2k8 R2 never include them. Additionally, when typing a table and then pressing a dot, no completions are returned that display the table's columns (even though I do get columns when connected to a 2k8 instance).

Is there something else I need to do and/or add to the FindCompletions logic in order to get support for SqlServer 2k8 R2 instances? And what about SqlServer 2k5 instances?

Thanks very much,

-Craig


select '?' excecuted against a server usingn management studio return a "?"

  

The server is MS SQL2008 R2 with collation Latin1_General_BIN2

I get the same problem when I put the statement in a unicode text file and execute using sqlcmd

Any pointers appreciated

Thanks

 

 

 


AJAX GridView Checkbox Select All in ASP.Net

  
In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Write Custom WebParts for SharePoint 2007

  
The popularity of SharePoint Portal Server 2003 led Microsoft to tightly integrate the next SharePoint version, Microsoft Office SharePoint Server (MOSS) 2007, with its ASP.NET 2.0 WebPart framework. This tight integration enables the ASP.NET and SharePoint developer to do a number of things that previously weren't possible

Serial Port Communication Read and Write using C#.net

  
In this simple article you will learn that how you can Open and read COM port data using System.IO.Ports Namespace.
Simply just create New Project of windows application in C#.

Import ServiceProcess APIs
C#:
---
using System.IO.Ports

VB
Imports System.IO.Ports

Write Parsing functions which parse port data according to attached device

ListView DataPager with SQL-Statements that already return only paged data.

  

Hi,

As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.


Select value in gridview

  

Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend