Technical Article

Object Search

,

This stored procedure allows you to search any database (or a combination of databases, including all) for a specific string found within column names, object names, and / or object definitions. As well, there are several optional input parameters which can be passed to the procedure to aid in narrowing down your search.

The quickest way to get started is to run the procedure as follows to get a detailed, overall listing of what it can do:

EXECUTE dbo.usp_Object_Search
     @Search_String = N''
    ,@Database_Name = N''

Which will output:

Search string is NULL or empty.

Usage:

    EXECUTE dbo.usp_Object_Search @Search_String, @Database_Name, @Search_Against, @Object_Type, @Exclude_String, @Hit_Limit, @Creation_Source

Input parameters (pass '?' for extended details):

    @Search_String (Mandatory)   : Search value

    @Database_Name (Mandatory)   : Database(s) to search

    @Search_Against (Optional)   : Search Object Name, Column Name, and / or Object Definition

    @Object_Type (Optional)      : Search Object Type(s)

    @Exclude_String (Optional)   : Exclude results which contain "@Exclude_String" value

    @Hit_Limit (Optional)        : Limit the rows returned

    @Creation_Source (Defaulted) : Search objects created by users, SQL Server, or both

Output (certain columns, indicated by an asterisk, will not be returned in the result set if they are not queried / matched against):

    database_name              : Database in which the matched object was found

    object_type                : Object type

    object_description         : Description of the object

    is_ms_shipped              : Indicates if the object was created by SQL Server

    object_name                : Name of the object in which the match was found

    column_name *              : Name of the column in which the match was found (if applicable)

    data_type *                : Data type of the "column_name" field

    data_length *              : Data length of the "data_type" field

    definition *               : Definition details in which the match was found (if applicable)

    search_criteria_matched_on : Type of match (Object, Column, Definition)

    row_count *                : Total rows (when a table object)

    total_space *              : Disk space allocated to the table object

    space_used *               : Space used by the table object (of the space allocated)

    space_data *               : Data space used by the table

    space_index *              : Index space used by the table

    space_unused *             : Unused space by the table object (of the space allocated)

Additionally, you can get specific details on parameter options by passing a question mark:

EXECUTE dbo.usp_Object_Search
     @Search_String = N'something'
    ,@Database_Name = N'tempdb'
    ,@Search_Against = N'?'

Which will output:

Valid Search Types (use single characters, combinations such as 'CD', 'NC', etc., or NULL for "ALL"):

C : Search Column Names

D : Search Object Definitions

N : Search Object Names

Or even:

EXECUTE dbo.usp_Object_Search
     @Search_String = N'something'
    ,@Database_Name = N'tempdb'
    ,@Search_Against = NULL
    ,@Object_Type = N'?'

Which will output:

Valid Object Types (use either a single value, multiple values separated by commas, or NULL for "All":

AF : Aggregate Function

C  : CHECK Constraint

D  : Default Or DEFAULT Constraint

F  : FOREIGN KEY Constraint

FN : Scalar Function

FS : Assembly Scalar-Function

FT : Assembly Table-Valued Function

IF : Inlined Table-Valued Function

IT : Internal Table

L  : Log

P  : Stored Procedure

PC : Assembly Stored Procedure

PG : Plan Guide

PK : PRIMARY KEY Constraint

R  : Rule

RF : Replication Filter Stored Procedure

S  : System Table

SN : Synonym

SO : Sequence

SQ : Service Queue

TA : Assembly DML Trigger

TF : Table-Valued Function

TR : Trigger

TT : Table Type

U  : User-Defined Table

UQ : UNIQUE Constraint

V  : View

X  : Extended Stored Procedure

And even:

EXECUTE dbo.usp_Object_Search
     @Search_String = N'something'
    ,@Database_Name = N'tempdb'
    ,@Search_Against = NULL
    ,@Object_Type = NULL
    ,@Exclude_String = NULL
    ,@Hit_Limit = NULL
    ,@Creation_Source = N'?'

Which will output:

Valid Creation Sources:

B : Both (User and System created objects)

S : System created objects only

U : User created objects only (Default)

You can also get a quick listing of all the available databases you can query by intentionally passing a non-existent database name as a parameter:

EXECUTE dbo.usp_Object_Search
     @Search_String = N'something'
    ,@Database_Name = N'?'

Which will output (for example):

Valid Database Names (use either a single value, multiple values separated by commas, or '*' for "All"):

master

model

msdb

tempdb

I've found the procedure to be very handy when making changes to a production database / server and I want to see which objects might be affected before going live.

Any friendly feedback is always welcome. Enjoy!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON


-----------------------------------------------------------------------------------------------------------------------------
--Error Trapping: Check If Procedure Already Exists And Create Shell If Applicable
-----------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.usp_Object_Search', N'P') IS NULL
BEGIN

EXECUTE ('CREATE PROCEDURE dbo.usp_Object_Search AS SELECT 1 AS shell')

END
GO


-----------------------------------------------------------------------------------------------------------------------------
--Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure
-----------------------------------------------------------------------------------------------------------------------------

--Purpose: Object Search
--Create Date (MM/DD/YYYY): 04/28/2009
--Developer: Sean Smith (s.smith.sql AT gmail DOT com)
--Additional Notes: N/A


-----------------------------------------------------------------------------------------------------------------------------
--Modification History: Listing Of All Modifications Since Original Implementation
-----------------------------------------------------------------------------------------------------------------------------

--Description: Added Schema Name To "object_name" Field
--           : Added "data_type" And "data_length" Fields
--           : Bug Fix For Available Database(s) Listing
--           : Minor Changes To Code Style
--Date (MM/DD/YYYY): 09/22/2011
--Developer: Sean Smith (s.smith.sql AT gmail DOT com)
--Additional Notes: N/A


--Description: Reformatted Code
--           : Bug Fixes
--           : Replaced Deprecated System Tables
--Date (MM/DD/YYYY): 09/24/2013
--Developer: Sean Smith (s.smith.sql AT gmail DOT com)
--Additional Notes: N/A


--Description: Added Support For Searching A Synonym's Base Object Name
--           : Added Option To Include System Objects In Search
--Date (MM/DD/YYYY): 05/23/2015
--Developer: Sean Smith (s.smith.sql AT gmail DOT com)
--Additional Notes: N/A


-----------------------------------------------------------------------------------------------------------------------------
--Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE dbo.usp_Object_Search

 @Search_String AS NVARCHAR (4000)
,@Database_Name AS NVARCHAR (4000)
,@Search_Against AS SYSNAME = NULL
,@Object_Type AS SYSNAME = NULL
,@Exclude_String AS SYSNAME = NULL
,@Hit_Limit AS SYSNAME = NULL
,@Creation_Source AS NVARCHAR (1) = N'U'

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647


DECLARE
 @Creation_Filter AS NVARCHAR (30)
,@Database_Search_Type AS INT
,@Message AS NVARCHAR (MAX)
,@Object_Prefix AS NVARCHAR (4)
,@SQL_Reusable AS VARCHAR (8000)
,@SQL_Start_Code AS VARCHAR (8000)
,@SQL_String AS VARCHAR (MAX)
,@Search_Against_Column_Names AS INT
,@Search_Against_Object_Definitions AS INT
,@Search_Against_Object_Names AS INT


DECLARE @Database_Names AS TABLE

(
database_name SYSNAME PRIMARY KEY CLUSTERED
)


SET @Creation_Filter = (CASE @Creation_Source
WHEN N'S' THEN N'AND O.is_ms_shipped = 1'
WHEN N'U' THEN N'AND O.is_ms_shipped = 0'
ELSE N''
END)


SET @Database_Name = ISNULL (@Database_Name, '')


SET @Database_Search_Type = 0


SET @Exclude_String = ISNULL (REPLACE (REPLACE (REPLACE (REPLACE (@Exclude_String, '[', '[[]'), '%', '[%]'), '_', '[_]'), '''', ''''''), '')


SET @Hit_Limit = NULLIF (@Hit_Limit, '')


SET @Object_Prefix = (CASE
WHEN @Creation_Source IN (N'B', N'S') THEN N'all_'
ELSE N''
END)


SET @Object_Type = ISNULL (REPLACE (REPLACE (@Object_Type, ' ', ''), ',', ''', '''), '')


SET @Search_Against = ISNULL (@Search_Against, '')


SET @Search_Against_Column_Names = (CASE
WHEN ISNULL (NULLIF (@Search_Against, ''), 'C') LIKE '%C%' THEN 1
ELSE 0
END)


SET @Search_Against_Object_Definitions = (CASE
WHEN ISNULL (NULLIF (@Search_Against, ''), 'D') LIKE '%D%' THEN 1
ELSE 0
END)


SET @Search_Against_Object_Names = (CASE
WHEN ISNULL (NULLIF (@Search_Against, ''), 'N') LIKE '%N%' THEN 1
ELSE 0
END)


SET @Search_String = ISNULL (REPLACE (REPLACE (REPLACE (REPLACE (@Search_String, '[', '[[]'), '%', '[%]'), '_', '[_]'), '''', ''''''), '')


SET @SQL_Reusable =

'
,O.[type] AS object_type
,(CASE
WHEN O.[type] = ''AF'' THEN ''Aggregate Function''
WHEN O.[type] = ''C'' THEN ''CHECK Constraint''
WHEN O.[type] = ''D'' THEN ''Default Or DEFAULT Constraint''
WHEN O.[type] = ''F'' THEN ''FOREIGN KEY Constraint''
WHEN O.[type] = ''FN'' THEN ''Scalar Function''
WHEN O.[type] = ''FS'' THEN ''Assembly Scalar Function''
WHEN O.[type] = ''FT'' THEN ''Assembly Table-Valued Function''
WHEN O.[type] = ''IF'' THEN ''Inlined Table-Valued Function''
WHEN O.[type] = ''IT'' THEN ''Internal Table''
WHEN O.[type] = ''L'' THEN ''Log''
WHEN O.[type] = ''P'' THEN ''Stored Procedure''
WHEN O.[type] = ''PC'' THEN ''Assembly Stored Procedure''
WHEN O.[type] = ''PG'' THEN ''Plan Guide''
WHEN O.[type] = ''PK'' THEN ''PRIMARY KEY Constraint''
WHEN O.[type] = ''R'' THEN ''Rule''
WHEN O.[type] = ''RF'' THEN ''Replication Filter Stored Procedure''
WHEN O.[type] = ''S'' THEN ''System Table''
WHEN O.[type] = ''SN'' THEN ''Synonym''
WHEN O.[type] = ''SO'' THEN ''Sequence''
WHEN O.[type] = ''SQ'' THEN ''Service Queue''
WHEN O.[type] = ''TA'' THEN ''Assembly DML Trigger''
WHEN O.[type] = ''TF'' THEN ''Table-Valued Function''
WHEN O.[type] = ''TR'' THEN ''Trigger''
WHEN O.[type] = ''TT'' THEN ''Table Type''
WHEN O.[type] = ''U'' THEN ''User-Defined Table''
WHEN O.[type] = ''UQ'' THEN ''UNIQUE Constraint''
WHEN O.[type] = ''V'' THEN ''View''
WHEN O.[type] = ''X'' THEN ''Extended Stored Procedure''
END) AS object_description
,(CASE
WHEN O.is_ms_shipped = 1 THEN ''X''
ELSE ''''
END) AS is_ms_shipped
,SCHEMA_NAME (O.[schema_id]) + N''.'' + O.name AS [object_name]
'


IF @Database_Name = '*'
BEGIN

SET @Database_Search_Type = 1


INSERT INTO @Database_Names

(
database_name
)

SELECT
DB.name AS database_name
FROM
master.sys.databases DB
WHERE
DB.[state] = 0


SET @Database_Name = (SELECT TOP (1) X.database_name FROM @Database_Names X ORDER BY X.database_name)

END
ELSE IF @Database_Name LIKE '%,%' AND @Database_Name NOT LIKE '%,,%' AND @Database_Name NOT LIKE ',%'
BEGIN

SET @Database_Search_Type = 2


IF @Database_Name NOT LIKE '%,'
BEGIN

SET @Database_Name = @Database_Name + ','

END


WHILE @Database_Name <> ''
BEGIN

INSERT INTO @Database_Names

(
database_name
)

SELECT
RTRIM (LTRIM (LEFT (@Database_Name, CHARINDEX (',', @Database_Name) - 1))) AS database_name


SET @Database_Name = REPLACE (@Database_Name, LEFT (@Database_Name, CHARINDEX (',', @Database_Name)), '')

END


SET @Database_Name =

(
SELECT TOP (1)
X.database_name
FROM
@Database_Names X
WHERE
NOT EXISTS

(
SELECT
*
FROM
master.sys.databases DB
WHERE
DB.[state] = 0
AND DB.name = X.database_name
)

ORDER BY
X.database_name
)


IF @Database_Name IS NULL
BEGIN

SET @Database_Name = (SELECT TOP (1) X.database_name FROM @Database_Names X ORDER BY X.database_name)

END

END


-----------------------------------------------------------------------------------------------------------------------------
--Error Trapping I: Check For Valid Parameters Being Passed To The Procedure
-----------------------------------------------------------------------------------------------------------------------------

IF @Search_String = ''
BEGIN

RAISERROR

(
 'Search string is NULL or empty.

Usage:

EXECUTE dbo.usp_Object_Search @Search_String, @Database_Name, @Search_Against, @Object_Type, @Exclude_String, @Hit_Limit, @Creation_Source


Input parameters (pass ''?'' for extended details):

@Search_String (Mandatory)   : Search value
@Database_Name (Mandatory)   : Database(s) to search
@Search_Against (Optional)   : Search Object Name, Column Name, and / or Object Definition
@Object_Type (Optional)      : Search Object Type(s)
@Exclude_String (Optional)   : Exclude results which contain "@Exclude_String" value
@Hit_Limit (Optional)        : Limit the rows returned
@Creation_Source (Defaulted) : Search objects created by users, SQL Server, or both


Output (certain columns, indicated by an asterisk, will not be returned in the result set if they are not queried / matched against):

database_name              : Database in which the matched object was found
object_type                : Object type
object_description         : Description of the object
is_ms_shipped              : Indicates if the object was created by SQL Server
object_name                : Name of the object in which the match was found
column_name *              : Name of the column in which the match was found (if applicable)
data_type *                : Data type of the "column_name" field
data_length *              : Data length of the "data_type" field
definition *               : Definition details in which the match was found (if applicable)
search_criteria_matched_on : Type of match (Object, Column, Definition)
row_count *                : Total rows (when a table object)
total_space *              : Disk space allocated to the table object
space_used *               : Space used by the table object (of the space allocated)
space_data *               : Data space used by the table
space_index *              : Index space used by the table
space_unused *             : Unused space by the table object (of the space allocated)'
,16
,1
)


RETURN

END


IF @Database_Search_Type <> 1
BEGIN

IF @Database_Name LIKE '%,,%'
BEGIN

RAISERROR

(
 'ERROR: Database search string contains multiple commas (''%s'').'
,16
,1
,@Database_Name
)


RETURN

END
ELSE IF @Database_Name LIKE ',%'
BEGIN

RAISERROR

(
 'ERROR: Database Name string cannot begin with comma(s) (''%s'').'
,16
,1
,@Database_Name
)


RETURN

END
ELSE IF NOT EXISTS (SELECT * FROM master.sys.databases DB WHERE DB.[state] = 0 AND DB.name = @Database_Name)
BEGIN

SELECT
@Message = COALESCE (@Message + CHAR (13), '') + DB.name
FROM
master.sys.databases DB
WHERE
DB.[state] = 0
ORDER BY
DB.name


RAISERROR

(
 'ERROR: Database ''%s'' not found on server.

Valid Database Names (use either a single value, multiple values separated by commas, or ''*'' for "All"):

%s'
,16
,1
,@Database_Name
,@Message
)


RETURN

END

END


IF @Search_Against_Column_Names + @Search_Against_Object_Definitions + @Search_Against_Object_Names = 0
BEGIN

RAISERROR

(
 'ERROR: ''%s'' is not a valid search type.

Valid Search Types (use single characters, combinations such as ''CD'', ''NC'', etc., or NULL for "ALL"):

C : Search Column Names
D : Search Object Definitions
N : Search Object Names'
,16
,1
,@Search_Against
)


RETURN

END


IF @Object_Type NOT LIKE '%,%' AND @Object_Type NOT IN ('', 'AF', 'C', 'D', 'F', 'FN', 'FS', 'FT', 'IF', 'IT', 'L', 'P', 'PC', 'PG', 'PK', 'R', 'RF', 'S', 'SN', 'SO', 'SQ', 'TA', 'TF', 'TR', 'TT', 'U', 'UQ', 'V', 'X')
BEGIN

RAISERROR

(
 'ERROR: ''%s'' is not a valid Object Type.

Valid Object Types (use either a single value, multiple values separated by commas, or NULL for "All":

AF : Aggregate Function
C  : CHECK Constraint
D  : Default Or DEFAULT Constraint
F  : FOREIGN KEY Constraint
FN : Scalar Function
FS : Assembly Scalar-Function
FT : Assembly Table-Valued Function
IF : Inlined Table-Valued Function
IT : Internal Table
L  : Log
P  : Stored Procedure
PC : Assembly Stored Procedure
PG : Plan Guide
PK : PRIMARY KEY Constraint
R  : Rule
RF : Replication Filter Stored Procedure
S  : System Table
SN : Synonym
SO : Sequence
SQ : Service Queue
TA : Assembly DML Trigger
TF : Table-Valued Function
TR : Trigger
TT : Table Type
U  : User-Defined Table
UQ : UNIQUE Constraint
V  : View
X  : Extended Stored Procedure'
,16
,1
,@Object_Type
)


RETURN

END


IF ISNUMERIC (@Hit_Limit) = 0 AND @Hit_Limit IS NOT NULL
BEGIN

RAISERROR

(
 'ERROR: @Hit_Limit value must be of type integer (''%s'').'
,16
,1
,@Hit_Limit
)


RETURN

END
ELSE IF @Hit_Limit <= 0
BEGIN

RAISERROR

(
 'ERROR: @Hit_Limit value must be greater than zero (''%s'').'
,16
,1
,@Hit_Limit
)


RETURN

END


IF @Creation_Source NOT IN (N'B', N'S', N'U')
BEGIN

RAISERROR

(
 'ERROR: ''%s'' is not a valid creation source.

Valid Creation Sources:

B : Both (User and System created objects)
S : System created objects only
U : User created objects only (Default)'
,16
,1
,@Creation_Source
)


RETURN

END


-----------------------------------------------------------------------------------------------------------------------------
--Error Trapping II: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
-----------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'tempdb.dbo.#temp_object_search', N'U') IS NOT NULL
BEGIN

DROP TABLE dbo.#temp_object_search

END


-----------------------------------------------------------------------------------------------------------------------------
--Soft Temp Table: Create / Modify Results Table
-----------------------------------------------------------------------------------------------------------------------------

CREATE TABLE dbo.#temp_object_search

(
 database_name NVARCHAR (1000) NOT NULL
,object_type VARCHAR (10) NOT NULL
,object_description VARCHAR (500) NULL
,is_ms_shipped VARCHAR (1) NULL
,[object_name] NVARCHAR (2000) NULL
,column_name NVARCHAR (1000) NULL
,data_type VARCHAR (250) NULL
,data_length VARCHAR (150) NULL
,[definition] NVARCHAR (MAX) NULL
,search_criteria_matched_on VARCHAR (50) NOT NULL
,row_count VARCHAR (50) NOT NULL
,total_space VARCHAR (50) NOT NULL
,space_used VARCHAR (50) NOT NULL
,space_data VARCHAR (50) NOT NULL
,space_index VARCHAR (50) NOT NULL
,space_unused VARCHAR (50) NOT NULL
)


IF @Search_Against_Column_Names = 0
BEGIN

ALTER TABLE dbo.#temp_object_search DROP COLUMN column_name
ALTER TABLE dbo.#temp_object_search DROP COLUMN data_type
ALTER TABLE dbo.#temp_object_search DROP COLUMN data_length

END


IF @Search_Against_Object_Definitions = 0
BEGIN

ALTER TABLE dbo.#temp_object_search DROP COLUMN [definition]

END


IF @Search_Against_Object_Names = 0
BEGIN

ALTER TABLE dbo.#temp_object_search DROP COLUMN row_count
ALTER TABLE dbo.#temp_object_search DROP COLUMN total_space
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_used
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_data
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_index
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_unused

END


-----------------------------------------------------------------------------------------------------------------------------
--Code Build I: Dynamically Construct SQL Code (Object Definitions / Object Names Portion)
-----------------------------------------------------------------------------------------------------------------------------

WHILE @Database_Name IS NOT NULL
BEGIN

SET @SQL_Start_Code =

'
USE [' + @Database_Name + ']


INSERT INTO dbo.#temp_object_search
'


IF @Search_Against_Column_Names = 1 AND @Search_Against_Object_Definitions + @Search_Against_Object_Names = 0
BEGIN

SET @SQL_String = ''


GOTO Query_Column_Only

END


SET @SQL_String =

'
SELECT
 ' + '''' + @Database_Name + '''' + ' AS database_name
'


SET @SQL_String = @SQL_String + @SQL_Reusable


IF @Search_Against_Column_Names = 1
BEGIN

SET @SQL_String = @SQL_String +

'
,'''' AS column_name
,'''' AS data_type
,'''' AS data_length
'

END


IF @Search_Against_Object_Definitions = 1
BEGIN

SET @SQL_String = @SQL_String +

'
,ISNULL (ISNULL (SQLM.[definition], SYN.base_object_name), '''') AS [definition]
'

END


IF @Search_Against_Object_Definitions + @Search_Against_Object_Names = 2
BEGIN

SET @SQL_String = @SQL_String +

'
,ISNULL ((CASE
WHEN O.name LIKE ' + '''%' + @Search_String + '%''' + ' THEN ''Object Name''
END), '''')
 + ISNULL ((CASE
WHEN O.name LIKE ' + '''%' + @Search_String + '%''' + ' AND ISNULL (SQLM.[definition], SYN.base_object_name) LIKE ' + '''%' + @Search_String + '%''' + ' THEN '' / ''
END), '''')
 + ISNULL ((CASE
WHEN ISNULL (SQLM.[definition], SYN.base_object_name) LIKE ' + '''%' + @Search_String + '%''' + ' THEN ''Definition''
END), '''') AS search_criteria_matched_on
'

END
ELSE BEGIN

IF @Search_Against_Object_Definitions = 1
BEGIN

SET @SQL_String = @SQL_String +

'
,''Definition'' AS search_criteria_matched_on
'

END
ELSE BEGIN

IF @Search_Against_Object_Names = 1
BEGIN

SET @SQL_String = @SQL_String +

'
,''Object Name'' AS search_criteria_matched_on
'

END

END

END


IF @Search_Against_Object_Names = 1
BEGIN

SET @SQL_String = @SQL_String +

'
,ISNULL (CONVERT (VARCHAR (50), sqDDPS.row_count), '''') AS row_count
,ISNULL (CONVERT (VARCHAR (50), CONVERT (DECIMAL (18,2), sqDDPS.reserved_pages)) + '' MB'', '''') AS total_space
,ISNULL (CONVERT (VARCHAR (50), CONVERT (DECIMAL (18,2), sqDDPS.data_pages + ISNULL ((CASE
WHEN sqDDPS.used_pages > sqDDPS.data_pages THEN (sqDDPS.used_pages - sqDDPS.data_pages)
END), 0))) + '' MB'', '''') AS space_used
,ISNULL (CONVERT (VARCHAR (50), CONVERT (DECIMAL (18,2), sqDDPS.data_pages)) + '' MB'', '''') AS space_data
,ISNULL (CONVERT (VARCHAR (50), CONVERT (DECIMAL (18,2), (CASE
WHEN sqDDPS.used_pages > sqDDPS.data_pages THEN (sqDDPS.used_pages - sqDDPS.data_pages)
WHEN sqDDPS.[object_id] IS NOT NULL THEN 0
END))) + '' MB'', '''') AS space_index
,ISNULL (CONVERT (VARCHAR (50), CONVERT (DECIMAL (18,2), (CASE
WHEN sqDDPS.reserved_pages > sqDDPS.used_pages THEN (sqDDPS.reserved_pages - sqDDPS.used_pages)
WHEN sqDDPS.[object_id] IS NOT NULL THEN 0
END))) + '' MB'', '''') AS space_unused
'

END


SET @SQL_String = @SQL_String +

'
FROM
sys.' + @Object_Prefix + 'objects O
'


IF @Search_Against_Object_Definitions = 1
BEGIN

SET @SQL_String = @SQL_String +

'
LEFT JOIN sys.' + @Object_Prefix + 'sql_modules SQLM ON SQLM.[object_id] = O.[object_id]
LEFT JOIN sys.synonyms SYN ON SYN.[object_id] = O.[object_id]
'

END


IF @Search_Against_Object_Names = 1
BEGIN

SET @SQL_String = @SQL_String +

'
LEFT JOIN

(
SELECT
 DDPS.[object_id]
,ISNULL (SUM (CASE
WHEN (DDPS.index_id < 2) THEN DDPS.row_count
END), 0) AS row_count
,SUM (DDPS.reserved_page_count * 0.0078125) AS reserved_pages
,SUM (DDPS.used_page_count * 0.0078125) AS used_pages
,SUM (CASE
WHEN (DDPS.index_id < 2) THEN (DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count) * 0.0078125
ELSE (DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count) * 0.0078125
END) AS data_pages
FROM
sys.dm_db_partition_stats DDPS
GROUP BY
DDPS.[object_id]
) sqDDPS ON sqDDPS.[object_id] = O.[object_id]
'

END


IF @Search_Against_Object_Definitions + @Search_Against_Object_Names = 2
BEGIN

SET @SQL_String = @SQL_String +

'
WHERE
(
ISNULL (SQLM.[definition], SYN.base_object_name) LIKE ' + '''%' + @Search_String + '%''' + '
OR O.name LIKE ' + '''%' + @Search_String + '%''' + '
)
'

END
ELSE BEGIN

IF @Search_Against_Object_Definitions = 1
BEGIN

SET @SQL_String = @SQL_String +

'
WHERE
ISNULL (SQLM.[definition], SYN.base_object_name) LIKE ' + '''%' + @Search_String + '%''' + '
'

END
ELSE BEGIN

IF @Search_Against_Object_Names = 1
BEGIN

SET @SQL_String = @SQL_String +

'
WHERE
O.name LIKE ' + '''%' + @Search_String + '%''' + '
'

END

END

END


SET @SQL_String = @SQL_String + @Creation_Filter


IF @Object_Type <> ''
BEGIN

SET @SQL_String = @SQL_String +

'
AND O.[type] IN (''' + @Object_Type + ''')
'

END


IF @Exclude_String <> ''
BEGIN

IF @Search_Against_Object_Definitions = 1
BEGIN

SET @SQL_String = @SQL_String +

'
AND ISNULL (ISNULL (SQLM.[definition], SYN.base_object_name), '''') NOT LIKE ' + '''%' + @Exclude_String + '%''' + '
'

END


IF @Search_Against_Object_Names = 1
BEGIN

SET @SQL_String = @SQL_String +

'
AND O.name NOT LIKE ' + '''%' + @Exclude_String + '%''' + '
'

END

END


IF @Search_Against_Column_Names = 0
BEGIN

GOTO Definition_Name_Only

END


SET @SQL_String = @SQL_String +

'
UNION ALL
'


-----------------------------------------------------------------------------------------------------------------------------
--Code Build II: Dynamically Construct SQL Code (Column Names Portion)
-----------------------------------------------------------------------------------------------------------------------------

Query_Column_Only:


SET @SQL_String = @SQL_String +

'
SELECT
 ' + '''' + @Database_Name + '''' + ' AS database_name
'


SET @SQL_String = @SQL_String + @SQL_Reusable


SET @SQL_String = @SQL_String +

'
,C.name AS column_name
,LOWER (TYPE_NAME (C.user_type_id) + ISNULL ((N'': [ '' + (CASE
WHEN C.system_type_id <> C.user_type_id THEN TYPE_NAME (C.system_type_id)
END) + N'' ]''), N'''')) AS data_type
,(CASE
WHEN LOWER (TYPE_NAME (C.system_type_id)) IN (N''nchar'', N''ntext'', N''nvarchar'') THEN CONVERT (VARCHAR (6), C.max_length / 2)
WHEN LOWER (TYPE_NAME (C.system_type_id)) NOT IN (N''bigint'', N''bit'', N''date'', N''datetime'', N''datetime2'', N''datetimeoffset'', N''decimal'', N''float'', N''int'', N''money'', N''numeric'', N''real'', N''smalldatetime'', N''smallint'', N''smallmoney'', N''time'', N''tinyint'') THEN CONVERT (VARCHAR (6), C.max_length)
ELSE CONVERT (VARCHAR (6), C.max_length) + '' ('' + CONVERT (VARCHAR (11), COLUMNPROPERTY (C.[object_id], C.name, ''Precision'')) + '','' + ISNULL (CONVERT (VARCHAR (11), COLUMNPROPERTY (C.[object_id], C.name, ''Scale'')), 0) + '')''
END) AS data_length
'


IF @Search_Against_Object_Definitions = 1
BEGIN

SET @SQL_String = @SQL_String +

'
,'''' AS [definition]
'

END


SET @SQL_String = @SQL_String +

'
,''Column Name'' AS search_criteria_matched_on
'


IF @Search_Against_Object_Names = 1
BEGIN

SET @SQL_String = @SQL_String +

'
,'''' AS row_count
,'''' AS total_space
,'''' AS space_used
,'''' AS space_data
,'''' AS space_index
,'''' AS space_unused
'

END


SET @SQL_String = @SQL_String +

'
FROM
sys.' + @Object_Prefix + 'objects O
INNER JOIN sys.' + @Object_Prefix + 'columns C ON C.[object_id] = O.[object_id]
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE
C.name LIKE ' + '''%' + @Search_String + '%''' + '
'

+ @Creation_Filter


IF @Object_Type <> ''
BEGIN

SET @SQL_String = @SQL_String +

'
AND O.[type] IN (''' + @Object_Type + ''')
'

END


IF @Exclude_String <> ''
BEGIN

SET @SQL_String = @SQL_String +

'
AND C.name NOT LIKE ' + '''%' + @Exclude_String + '%''' + '
'

END


Definition_Name_Only:


EXECUTE (@SQL_Start_Code + @SQL_String)


IF @Database_Search_Type = 0
BEGIN

SET @Database_Name = NULL

END
ELSE BEGIN

SET @Database_Name = (SELECT TOP (1) X.database_name FROM @Database_Names X WHERE X.database_name > @Database_Name ORDER BY X.database_name)

END

END


-----------------------------------------------------------------------------------------------------------------------------
--Main Query: Final Display / Output
-----------------------------------------------------------------------------------------------------------------------------

SET @SQL_String =

'
SELECT' + (CASE WHEN @Hit_Limit > 0 THEN ' TOP (' + CONVERT (VARCHAR (10), @Hit_Limit) + ')' ELSE '' END) + '
UT.*
FROM
dbo.#temp_object_search UT
ORDER BY
 UT.database_name
,UT.is_ms_shipped
,UT.object_type
,UT.[object_name]
,UT.search_criteria_matched_on
'


IF @Search_Against_Column_Names = 1
BEGIN

IF (SELECT COUNT (DISTINCT X.column_name) FROM dbo.#temp_object_search X) <= 1
BEGIN

IF (SELECT TOP (1) Y.column_name FROM dbo.#temp_object_search Y) = ''
BEGIN

ALTER TABLE dbo.#temp_object_search DROP COLUMN column_name
ALTER TABLE dbo.#temp_object_search DROP COLUMN data_type
ALTER TABLE dbo.#temp_object_search DROP COLUMN data_length

END

END
ELSE BEGIN

SET @SQL_String = @SQL_String +

'
,UT.column_name
,UT.data_type
,UT.data_length
'

END

END


IF @Search_Against_Object_Definitions = 1
BEGIN

IF (SELECT COUNT (DISTINCT X.[definition]) FROM dbo.#temp_object_search X) <= 1
BEGIN

IF (SELECT TOP (1) Y.[definition] FROM dbo.#temp_object_search Y) = ''
BEGIN

ALTER TABLE dbo.#temp_object_search DROP COLUMN [definition]

END

END

END


IF @Search_Against_Object_Names = 1
BEGIN

IF (SELECT COUNT (DISTINCT X.row_count) FROM dbo.#temp_object_search X) <= 1
BEGIN

IF (SELECT TOP (1) Y.row_count FROM dbo.#temp_object_search Y) = ''
BEGIN

ALTER TABLE dbo.#temp_object_search DROP COLUMN row_count
ALTER TABLE dbo.#temp_object_search DROP COLUMN total_space
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_used
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_data
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_index
ALTER TABLE dbo.#temp_object_search DROP COLUMN space_unused

END

END

END


EXECUTE (@SQL_String)
GO

Rate

5 (19)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (19)

You rated this post out of 5. Change rating