Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘Transact SQL T-SQL’

Find Foreign Keys in Sql Server

Posted by Viral Sarvaiya on October 12, 2012


Hi,

Here is the query to find list of foreign keys of the particular database,

Here this query gives name of foreign keys, table name, column name, referenced table name and referenced column name.


;With CTE As
(
Select Object_Name(constraint_object_id) Constraint_Name, Object_Name(parent_object_id) Table_Name, C.name Column_Name
From sys.foreign_key_columns FK
Inner Join sys.columns C On FK.parent_object_id = C.object_id And FK.parent_column_id = C.column_id
)
SELECT C.Constraint_Name,C.Table_Name,C.Column_Name, Object_Name(FK.referenced_object_id) Referenced_Table_Name,SC.name Referenced_Column_Name
from CTE C
Inner Join sys.foreign_key_columns FK On C.Constraint_Name = Object_Name(FK.constraint_object_id)
Inner Join sys.columns SC On FK.referenced_object_id = SC.object_id And FK.referenced_column_id = SC.column_id

Thanks.

Advertisements

Posted in Sql Server | Tagged: , , , , , , , , | 1 Comment »

ROWCOUNT in Sql server

Posted by Viral Sarvaiya on June 6, 2012


Causes SQL Server to stop processing the query after the specified number of rows are returned.

SET ROWCOUNT 10;
 SELECT * FROM Employee;

If Employee table has 100 reocrds, query return only 10 records. SET ROWCOUNT works as a TOP.

Syntex:
SET ROWCOUNT { number | @number_var }

where “number | @number_var” is Integer value rows to be processed before stopping the specific query.

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

SET ROWCOUNT is not affect DELETE, INSERT and UPDATE statement so for that similar behavior use TOP in select Statement.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

Posted in Sql Server | Tagged: , , , , , | Leave a Comment »

SQL Server: Return Multiple Values from a Function

Posted by Viral Sarvaiya on February 10, 2011


hi

we know that function can return value and stored procedure cannot return value.

but function can return more than 1 value,

but the return type should be table.

so how can we do that..?

Click here.

thank you…

Posted in Sql Server | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: