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