<

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

Published on
5,878 Points
2,778 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
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
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.
Learn from the best.