<< Enterprise Library for Windows Azure with Autoscaling | Home | Database Model Diagram >>

Ways to search for column name, table name, or stored procedure text in SQL

This is nothing new, just a way for me to organize the information better.  Personally, I like the first 2 ways better just because I can order the result set any way I want.

USE {Database Name}
GO

-- SQL 2008, search table name
SELECT IS_C.COLUMN_NAME
    , IS_C.*
FROM INFORMATION_SCHEMA.COLUMNS AS IS_C
WHERE IS_C.TABLE_NAME = '{Table Name}'
ORDER BY IS_C.COLUMN_NAME  -- , ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE

-- SQL 2008, search column name
SELECT IS_C.COLUMN_NAME
    , IS_C.*
FROM INFORMATION_SCHEMA.COLUMNS AS IS_C
WHERE IS_C.COLUMN_NAME = '{column Name}'
ORDER BY IS_C.COLUMN_NAME  -- , ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE

-- SQL 2005, search table name
SELECT S_T.name AS TableName
    , S_C.name AS ColumnName
FROM SYS.COLUMNS AS S_C
    INNER JOIN SYS.tables AS S_T
        ON S_C.object_id = S_T.object_id
WHERE S_T.name = '{Table Name}'
ORDER BY S_C.name

-- Using Stored Procedure to search table name
sp_columns '{Table Name}'



-- SQL 2008, search stored procedure text
SELECT ROUTINE_NAME
    , ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SrcDBs%'
    AND ROUTINE_TYPE='PROCEDURE'

-- or, search stored procedure text
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%SrcDBs%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

Print | posted on Tuesday, March 6, 2012 11:38 AM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski