Finding stored procedures, tables, columns, constraints, etc., from a SQL Server Database.
Published on
5,911 Points
Hi,
I think this will be useful for all the SQL Server Developers and SQL Server DBAs.
Below are the queries to find stored procedures, tables, columns, constraints in a database.
1. From the below query you can find stored procedures, tables, constraints, etc. of a particular database:
select * from sys.all_objects where type_desc = ''
and name like '%%'
The following are the type of the objects you can search for ():
CLR_STORED_PROCEDURE
DEFAULT_CONSTRAINT
EXTENDED_STORED_PROCEDURE
FOREIGN_KEY_CONSTRAINT
INTERNAL_TABLE
PRIMARY_KEY_CONSTRAINT
SERVICE_QUEUE
SQL_INLINE_TABLE_VALUED_FU
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TABLE_VALUED_FUNCTION
SYSTEM_TABLE
UNIQUE_CONSTRAINT
USER_TABLE
VIEW
2. From the below query you can search for columns from the entire user defined tables of a particular database:
select * from sys.all_objects where type_desc = 'USER_TABLE'
and object_id
in (select object_id from sys.all_columns where name like '%%')
3. From the below two queries you can search for any object (tables, columns, variables, etc.) from all the Stored Procedures of a particular database: First query is more efficient than second one but the execution time is little bit high.
a. select o.name, o.type_desc,* from sys.all_sql_modules m inner join sys.all_objects o on o.object_id = m.object_id
where m.definition like '%%' and o.type_desc = 'SQL_STORED_PROCEDURE' order by o.type_desc, o.name
b. SELECT
ROUTINE_NAME,
ROUTINE_TYPE
FROM
INFORMATION_SCHEMA.ROUTINE
WHERE
ROUTINE_DEFINITION LIKE '%%'
AND ROUTINE_TYPE='Procedure'
All the Best.
I think this will be useful for all the SQL Server Developers and SQL Server DBAs.
Below are the queries to find stored procedures, tables, columns, constraints in a database.
1. From the below query you can find stored procedures, tables, constraints, etc. of a particular database:
select * from sys.all_objects where type_desc = ''
and name like '%%'
The following are the type of the objects you can search for ():
CLR_STORED_PROCEDURE
DEFAULT_CONSTRAINT
EXTENDED_STORED_PROCEDURE
FOREIGN_KEY_CONSTRAINT
INTERNAL_TABLE
PRIMARY_KEY_CONSTRAINT
SERVICE_QUEUE
SQL_INLINE_TABLE_VALUED_FU
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TABLE_VALUED_FUNCTION
SYSTEM_TABLE
UNIQUE_CONSTRAINT
USER_TABLE
VIEW
2. From the below query you can search for columns from the entire user defined tables of a particular database:
select * from sys.all_objects where type_desc = 'USER_TABLE'
and object_id
in (select object_id from sys.all_columns where name like '%%')
3. From the below two queries you can search for any object (tables, columns, variables, etc.) from all the Stored Procedures of a particular database: First query is more efficient than second one but the execution time is little bit high.
a. select o.name, o.type_desc,* from sys.all_sql_modules m inner join sys.all_objects o on o.object_id = m.object_id
where m.definition like '%%' and o.type_desc = 'SQL_STORED_PROCEDURE' order by o.type_desc, o.name
b. SELECT
ROUTINE_NAME,
ROUTINE_TYPE
FROM
INFORMATION_SCHEMA.ROUTINE
WHERE
ROUTINE_DEFINITION LIKE '%%'
AND ROUTINE_TYPE='Procedure'
All the Best.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.