<

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

Published on
5,746 Points
2,646 Views
1 Endorsement
Last Modified:
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_FUNCTION
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.ROUTINES
WHERE
      ROUTINE_DEFINITION LIKE '%%'
         AND ROUTINE_TYPE='Procedure'

All the Best.
1
Comment
0 Comments

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Join & Write a Comment

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month