text: 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