Finding stored procedures, tables, columns, constraints, etc., from a SQL Server Database.

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 NCTION
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 S
WHERE
      ROUTINE_DEFINITION LIKE '%%'
         AND ROUTINE_TYPE='Procedure'

All the Best.