Home » mssql » SQL Server: how to search for a table name or a column name or a given stored procedure name

SQL Server: how to search for a table name or a column name or a given stored procedure name

Posted on by

This article demonstrates how the Microsoft SQL Server database can be queried to perform the following tasks:
1. fetch a list of tables.
2. fetch a list of Columns.
3. fetch a list of Stored Procedures
The results can refined further by including other search criteria in the where clause of each query.

--Query to fetch the list of table that match a pattern
select * from information_schema.tables where table_name like '%Employee%'

--Query to fetch the list of Columns (with table info) that match a pattern.
select * from information_schema.columns where column_name like '%EmpId%'

--Query to fetch the list of Stored Procedures that match a pattern.
select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and routine_name like '%MyProc%' 

http://cavemansblog.wordpress.com/2009/02/26/sql-how-to-2/

Leave a comment