Xtype: Object type. Can be one of the following types of objects: C = CHECK Constraint D = default value or defaults constraint f = FOREIGN key constraint L = log fn = scalar function if = inline table function p = Stored procedure pk = PRIMARY KEY constraint (type is K RF = Copy Filter stored procedure s = system table TF = Table function TR = trigger u = User table UQ = UNIQUE constraint (type K) V = view x = Extended stored Procedure1. Use SQL statements to find stored procedures, triggers, functions, and so on that contain a keyword
SELECT from as s INNER JOIN as on = s.id WHERE TEXT like ' % keyword% ' and = ' P '
--querying stored procedures and functionsSELECTRoutine_name, Routine_definition frominformation_schema. ROUTINESWHERERoutine_definition like '% keyword%' andRoutine_type='PROCEDURE'--querying the parameters of stored procedures and functionsSelect * frominformation_schema. PARAMETERS--querying tables and views of a databaseSelect * fromInformation_schema. TABLES
2. Querying foreign key relationships
SELECTpt.name Reference table name, pc.name reference column name, rt.name referenced table name, rc.name referenced column name fromSys.foreign_key_columnsJOINSys.objects PT onsys.foreign_key_columns.parent_object_id=Pt.object_idJOINSys.objects RT onsys.foreign_key_columns.referenced_object_id=Rt.object_idJOINSys.columns PC onsys.foreign_key_columns.parent_object_id=Pc.object_id andsys.foreign_key_columns.parent_column_id=pc.column_idJOINSys.columns RC onsys.foreign_key_columns.referenced_object_id=RC.object_id andsys.foreign_key_columns.referenced_column_id=rc.column_idwhereRt.name='Biz_audit_materialbill'
SQL Server Utility query script