<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
5,800 Points
2,700 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

The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month