Technical Article

Find for SQL Objects, Entities or Text

,

This Stored Procedure will help you to find database objects such as tables, stored procedures, views or function by its name. You can find for table(s) having a specific column.

While working on project we often do impact analysis. Most of the changes in application directly affect the database. Hence we need to figure out the tables having a particular column and the stored procedures using the column in the quries written inside them. I used to do the same task in my project work.

To minimize the time effort involved in such tasks I though to have a basic tool which will perform a quick search for me and return list of almost all objects (tables, stored procedures, views....) mathcing the specified condition.

So I created a stored procedure which will do the job for me.

Lets have a look at the code one by one.

Stored Procedure Name: uspFindObjects

Parameters of Stored Procedure.

1. @ObjectType

Type of object to search. You search for a table or stored procedure by its name. Following @ObjectType can be passed to stored procedure.

- U = Table

- P = Stored Procedure

- V = View

- FN = Function

- * = Find All (Tables, SP, Views, Functions)

2. @SearchText

Text to search

3. @SearchType

Type of data to search

- C = Search for a column

- T = Search for a text

4. @OrderBy

This specifies the field by which order the result should be returned. This parameter is optinal. By defult result is return in order of object name (@OrderBy='N'). Anything other by 'N' is Object type.

Lets have an example to understand it better.

DataBase: MyTestDatabase

We have a table Client_Master with following structure.

Table Name : Client_Master

Column Name Data Type
Client_ID NUMERIC (10)
Client_Name VARCHAR (100)

Then lets have a stored procedure spClientList as

CREATE STORED PROCEDURE spClientList AS

( Select Client_ID,Client_Name FROM Client_Master)

Now we can perform search as displayed below:

1. Seach for the table named Client_Master in list of tables

EXEC uspFindObjects 'U','Client_Master','N'

2. Seach for the SP named spClientList in entire database

EXEC uspFindObjects '*','spClientList','N'

3. Seach for the column Client_ID in tables

EXEC uspFindObjects 'U','Client_ID','C'

4. Seach for the column Client_ID in stored procedures

EXEC uspFindObjects 'P','Client_ID','T'

5. Seach for the column Client_ID in all SP/Views/Functions in entire database

EXEC uspFindObjects '*','Client_ID','T'

This way you can search for the specific objects in a database.

I have provided the comments in stored procedure code to understand it better. Hence I will not explain rest fo the code here again.

Hope the code helps you and provides better understanding of how to look for DB objects in system tables.

IF EXISTS (SELECT * FROM Sys.Objects WHERE object_id = OBJECT_ID(N'[dbo].[uspFindObjects]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspFindObjects]
GO

CREATE PROCEDURE uspFindObjects
    @ObjectType CHAR(2)=NULL,
    @SearchText VARCHAR(500) = NULL,
    @SearchType CHAR(1)=NULL,
    @OrderBy CHAR(1)='N'
AS
BEGIN
/***********************************************************************************************************
Procedure:    uspFindObjects

Parameters: 1. @ObjectType    -    Type of object to be searched.
                    - U        :    Table
                    - P        :    Stored Procedure
                    - V        :    View
                    - FN    :    Function
                    - *        :    Find All (Tables, SP, Views, Functions)
            2. @SearchText    -    Text to be searched
            3. @SearchType    -    Type of data to be searched
                    - C        :    Column
                    - T        :    Text
            4. @OrderBy        -    Order of results to be returned. Default is by object name.
                    - N        :    ObjectName
                    Anything other than N is order by ObjectType

Purpose:    1. Find tables/stored procedures/functions/views by name. 
            2. Find tables having specific columns.
            3. Find a string inside stored procedure/views/functions.

Written by:    Sunil M. Chandurkar

Tested on:     SQL Server 2005

Date created: October 18, 2007

Example 1:    To search table with a name:
        
        EXEC uspFindObjects 'U','Client_Master','N'

Example 2:     To search table/stored procedure/views with specific name
        
        EXEC uspFindObjects '*','Client_Master','N'

Example 3:    To seach tables having specific columns

        EXEC uspFindObjects 'U','Client_ID','C'

Example 4:    To search text inside any Stored Procedure/View/Function

        EXEC uspFindObjects '*','City_Name','T'

***********************************************************************************************************/
    SET NOCOUNT ON
    CREATE TABLE #sysObjects
    (
        ObjectName VARCHAR(200),
        ObjectType VARCHAR(200)
    )
    
    SET @ObjectType = ISNULL(LTRIM(RTRIM(@ObjectType)),'')
    
    IF @ObjectType=''
    BEGIN
        PRINT 'Procedure ''uspFindObjects'' expects parameter ''@ObjectType'', which was not supplied.'
        RETURN
    END

    SET @SearchText = ISNULL(LTRIM(RTRIM(@SearchText)),'')
    IF @SearchText=''
    BEGIN
        PRINT 'Procedure ''uspFindObjects'' expects parameter ''@SearchText'', which was not supplied.'
        RETURN
    END
    SET @SearchType = ISNULL(LTRIM(RTRIM(@SearchType)),'N')
--Here search for an object by its name.
--E.g. Look for a table having a specific name
    IF LTRIM(RTRIM(@SearchType))='N'
        INSERT INTO #sysObjects
        SELECT    so.name,
                CASE so.XType    WHEN 'P' THEN 'Stored Procedure'
                                WHEN 'U' THEN 'TABLE'
                                WHEN 'V' THEN 'View'
                                WHEN 'FN' THEN 'Function'
                END 'Type'
        FROM    sysobjects so 
        WHERE    so.Name Like '%' + @SearchText +'%' AND 
                (@ObjectType='*' OR so.xtype=@ObjectType)
--search for text in stored procedures or views
    ELSE IF LTRIM(RTRIM(@SearchType))='T'
        INSERT INTO #sysObjects
        SELECT    so.name,
                CASE so.XType    WHEN 'P' THEN 'Stored Procedure'
                                WHEN 'U' THEN 'TABLE'
                                WHEN 'V' THEN 'View'
                                WHEN 'FN' THEN 'Function'
                END 'Type'
        FROM    SysObjects so INNER JOIN SysComments SCM ON so.id=scm.id AND scm.text LIKE '%' + @SearchText +'%' 
                AND (@ObjectType='*' OR so.xtype=@ObjectType)
--search for columns in tables
    ELSE IF LTRIM(RTRIM(@SearchType))='C'
        INSERT INTO #sysObjects
        SELECT    so.name,
                CASE so.XType    WHEN 'P' THEN 'Stored Procedure'
                                WHEN 'U' THEN 'TABLE'
                                WHEN 'V' THEN 'View'
                                WHEN 'FN' THEN 'Function'
                END 'Type'
        FROM    SysObjects so INNER JOIN syscolumns sc ON so.id=sc.id AND sc.name LIKE '%' + @SearchText +'%'
                AND (@ObjectType='*' OR so.xtype=@ObjectType)

    SET NOCOUNT OFF

    IF @OrderBy='N'
        SELECT * FROM #sysObjects ORDER BY ObjectName
    ELSE
        SELECT * FROM #sysObjects ORDER BY ObjectType,ObjectName

    SET NOCOUNT OFF
END
GO

Rate

4.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4.67 (3)

You rated this post out of 5. Change rating