Get Parameter List from StoreProcedure or Function in SqlServer 2005

–get all storeprocedure and function

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

–pass storeprocedure name or function name

–get the relavent columns ParameterName,DataType,Length,ParameterType

SELECT Parameter_name as ParameterName,Data_type as DataType,coalesce(Character_Maximum_Length,0) as Length,Parameter_Mode as ParameterType FROM INFORMATION_SCHEMA.PARAMETERS

WHERE SPECIFIC_NAME=’sp_InsertOrder’

Get Primary key – Foreign key relations table in sql server 2005

Why it is required?

The main purpose of this is finding related constraint table from database.

Check below Query.

–get table list with constraint(primary and foreign key)

select * from information_schema.constraint_column_usage

–get table list with foreign key constraint

select * from information_schema.referential_constraints

–get the relation

select

tblAll.table_name as PrimaryTableName,

tblAll.column_name as PrimaryTableColumn,

tblFK.table_name as ForeignKeyTable,

tblFK.column_name as ForeignKeyColumn

from information_schema.constraint_column_usage tblAll

inner join information_schema.referential_constraints tblAllFK on tblAllFK.unique_constraint_name = tblAll.constraint_name

inner join information_schema.constraint_column_usage tblFK on tblAllFK.constraint_name=tblFK.constraint_name

Thnx