USE [AMC_Support]
GO
/****** Object: StoredProcedure [dbo].[usp_ssi_String_Search] Script Date: 12/17/2015 14:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------------------------------------------------------------------
-- Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure
-----------------------------------------------------------------------------------------------------------------------------
-- Purpose: Search For A String Value Within Columns Of Data Types CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, XML
-- Create Date (MM/DD/YYYY): 03/20/2012
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Modification History: Listing Of All Modifications Since Original Implementation
-----------------------------------------------------------------------------------------------------------------------------
-- Description: No Modifications To Date
-- Date (MM/DD/YYYY): N/A
-- Developer: N/A
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[usp_ssi_String_Search]
@Search_String AS NVARCHAR (500)
,@Database_Name AS NVARCHAR (300)
,@Object_Types AS NVARCHAR (10) = NULL
,@Data_Types AS NVARCHAR (100) = NULL
,@Table_Max_Rows AS BIGINT = NULL
,@Column_Max_Length AS SMALLINT = NULL
WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
DECLARE
@Loop_Column_Number AS BIGINT
,@Loop_Column_Number_First AS BIGINT
,@Loop_Object_Number AS BIGINT
,@Object_Name AS SYSNAME
,@Schema_Name AS SYSNAME
,@SQL_String_Full AS NVARCHAR (MAX)
,@SQL_String_IN_Column_Name AS NVARCHAR (MAX)
,@SQL_String_SELECT AS NVARCHAR (MAX)
,@SQL_String_WHERE AS NVARCHAR (MAX)
SET @Object_Types = N'''' + REPLACE (REPLACE (NULLIF (@Object_Types, N''), N' ', N''), N',', N''',''') + N''''
SET @Data_Types = N'''' + REPLACE (REPLACE (NULLIF (@Data_Types, N''), N' ', N''), N',', N''',''') + N''''
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping I: Validate "@Search_String" And "@Database_Name" Input Parameter Values
-----------------------------------------------------------------------------------------------------------------------------
IF NULLIF (@Search_String, N'') IS NOT NULL
BEGIN
SET @Search_String = REPLACE (REPLACE (REPLACE (REPLACE (@Search_String, N'[', N'[[]'), N'%', N'[%]'), N'_', N'[_]'), N'''', N'''''')
END
ELSE BEGIN
RAISERROR
(
N'ERROR: @Search_String input parameter cannot be blank or NULL.'
,16
,1
)
RETURN
END
IF EXISTS (SELECT * FROM master.sys.databases DB WHERE DB.name = @Database_Name)
BEGIN
SET @Database_Name = QUOTENAME (@Database_Name)
END
ELSE IF EXISTS (SELECT * FROM master.sys.databases DB WHERE QUOTENAME (DB.name) = @Database_Name + N']')
BEGIN
SET @Database_Name = @Database_Name + N']'
END
ELSE IF NOT EXISTS (SELECT * FROM master.sys.databases DB WHERE QUOTENAME (DB.name) = @Database_Name)
BEGIN
RAISERROR
(
N'ERROR: Database (''%s'') does not exist. Make sure that the name is entered correctly.'
,16
,1
,@Database_Name
)
RETURN
END
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping II: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_objects_columns', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_objects_columns
END
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_results', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_results
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Creation: Create Temp Tables To Temporarily Store Searchable Objects / Columns And Output Results
-----------------------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.#temp_string_search_objects_columns
(
object_type VARCHAR (2) NOT NULL
,data_type SYSNAME NOT NULL
,max_length SMALLINT NOT NULL
,[schema_name] SYSNAME NOT NULL
,[object_name] SYSNAME NOT NULL
,column_name SYSNAME NOT NULL
,schema_object_dense_rank BIGINT NOT NULL
,column_row_number BIGINT UNIQUE NOT NULL
,PRIMARY KEY CLUSTERED
(
schema_object_dense_rank
,column_row_number
)
)
CREATE TABLE dbo.#temp_string_search_results
(
[schema_name] SYSNAME NOT NULL
,[object_name] SYSNAME NOT NULL
,column_name SYSNAME NOT NULL
,column_data NVARCHAR (MAX) NOT NULL
,occurrences INT NOT NULL
)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update I: Insert Searchable Objects / Columns Into Temp Table
-----------------------------------------------------------------------------------------------------------------------------
SET @SQL_String_Full =
N'
SELECT
O.[type] AS object_type
,T.name AS data_type
,C.max_length
,S.name AS [schema_name]
,O.name AS [object_name]
,C.name AS column_name
,DENSE_RANK () OVER
(
ORDER BY
S.name
,O.name
) AS schema_object_dense_rank
,ROW_NUMBER () OVER
(
ORDER BY
C.name
) AS column_row_number
FROM
' + @Database_Name + N'.sys.schemas S
INNER JOIN ' + @Database_Name + N'.sys.objects O ON O.[schema_id] = S.[schema_id]
AND O.[type] IN (' + (CASE
WHEN @Object_Types IS NOT NULL THEN @Object_Types
ELSE N'''U'', ''V'''
END) + N')
INNER JOIN ' + @Database_Name + N'.sys.columns C ON C.[object_id] = O.[object_id]
'
IF @Column_Max_Length > 0
BEGIN
SET @SQL_String_Full = @SQL_String_Full +
N'
' + NCHAR (9) + N'AND C.system_type_id IN (167, 175, 231, 239)
' + NCHAR (9) + N'AND C.max_length BETWEEN 1 AND ' + CONVERT (NVARCHAR (6), @Column_Max_Length) + N'
'
END
ELSE BEGIN
SET @SQL_String_Full = @SQL_String_Full +
N'
' + NCHAR (9) + N'AND C.system_type_id IN (35, 99, 167, 175, 231, 239, 241)
'
END
SET @SQL_String_Full = @SQL_String_Full +
N'
INNER JOIN ' + @Database_Name + N'.sys.types T ON T.system_type_id = C.system_type_id
AND T.user_type_id = C.user_type_id
' + (CASE
WHEN @Data_Types IS NOT NULL THEN REPLICATE (NCHAR (9), 3) + N'AND T.name IN (' + @Data_Types + N')'
ELSE N''
END)
IF @Table_Max_Rows > 0
BEGIN
SET @SQL_String_Full = @SQL_String_Full +
N'
INNER JOIN
(
SELECT
DDPS.[object_id]
FROM
' + @Database_Name + N'.sys.dm_db_partition_stats DDPS
WHERE
DDPS.index_id < 2
GROUP BY
DDPS.[object_id]
HAVING
SUM (DDPS.row_count) <= ' + CONVERT (NVARCHAR (20), @Table_Max_Rows) + N'
) sqTMR ON sqTMR.[object_id] = O.[object_id]
'
END
INSERT INTO dbo.#temp_string_search_objects_columns
(
object_type
,data_type
,max_length
,[schema_name]
,[object_name]
,column_name
,schema_object_dense_rank
,column_row_number
)
EXECUTE (@SQL_String_Full)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update II: Insert Matched Results Into Temp Table
-----------------------------------------------------------------------------------------------------------------------------
SELECT TOP (1)
@Loop_Object_Number = X.schema_object_dense_rank
,@Schema_Name = X.[schema_name]
,@Object_Name = X.[object_name]
FROM
dbo.#temp_string_search_objects_columns X
ORDER BY
X.schema_object_dense_rank
WHILE @Loop_Object_Number IS NOT NULL
BEGIN
SET @SQL_String_SELECT = N''
SET @SQL_String_IN_Column_Name = N''
SET @SQL_String_WHERE = N''
SET @Loop_Column_Number = (SELECT MIN (X.column_row_number) FROM dbo.#temp_string_search_objects_columns X WHERE X.schema_object_dense_rank = @Loop_Object_Number)
SET @Loop_Column_Number_First = @Loop_Column_Number
WHILE @Loop_Column_Number IS NOT NULL
BEGIN
SELECT
@SQL_String_SELECT = @SQL_String_SELECT + (CASE
WHEN @Loop_Column_Number = @Loop_Column_Number_First THEN N'SELECT' + NCHAR (13) + REPLICATE (NCHAR (9), 7) + N' '
ELSE NCHAR (13) + REPLICATE (NCHAR (9), 7) + N','
END) + N'CONVERT (NVARCHAR (MAX), (CASE
WHEN ' + (CASE
WHEN X.data_type = N'XML' THEN N'CONVERT (NVARCHAR (MAX), '
ELSE N''
END) + QUOTENAME (X.column_name) + (CASE
WHEN X.data_type = N'XML' THEN N')'
ELSE N''
END) + N' LIKE ''%' + @Search_String + N'%'' THEN ' + QUOTENAME (X.column_name) + N'
ELSE NULL
END)) AS ' + QUOTENAME (X.column_name)
,@SQL_String_IN_Column_Name = @SQL_String_IN_Column_Name + (CASE
WHEN @Loop_Column_Number = @Loop_Column_Number_First THEN N''
ELSE N', '
END) + QUOTENAME (X.column_name)
,@SQL_String_WHERE = @SQL_String_WHERE + (CASE
WHEN @Loop_Column_Number = @Loop_Column_Number_First THEN NCHAR (13) + REPLICATE (NCHAR (9), 6) + N'WHERE' + NCHAR (13) + REPLICATE (NCHAR (9), 7)
ELSE NCHAR (13) + REPLICATE (NCHAR (9), 7) + N'OR '
END) + (CASE
WHEN X.data_type = N'XML' THEN N'CONVERT (NVARCHAR (MAX), '
ELSE N''
END) + QUOTENAME (X.column_name) + (CASE
WHEN X.data_type = N'XML' THEN N')'
ELSE N''
END) + N' LIKE ''%' + @Search_String + N'%'''
FROM
dbo.#temp_string_search_objects_columns X
WHERE
X.schema_object_dense_rank = @Loop_Object_Number
AND X.column_row_number = @Loop_Column_Number
SET @Loop_Column_Number = (SELECT MIN (X.column_row_number) FROM dbo.#temp_string_search_objects_columns X WHERE X.schema_object_dense_rank = @Loop_Object_Number AND X.column_row_number > @Loop_Column_Number)
END
SET @SQL_String_Full =
(
N'
SELECT
''' + @Schema_Name + N''' AS [schema_name]
,''' + @Object_Name + N''' AS [object_name]
,UNPV.column_name
,UNPV.column_data
,COUNT (*) AS occurrences
FROM
(
'
+ @SQL_String_SELECT
+ NCHAR (13) + REPLICATE (NCHAR (9), 6) + N'FROM' + NCHAR (13)
+ REPLICATE (NCHAR (9), 7) + @Database_Name
+ N'.'
+ QUOTENAME (@Schema_Name)
+ N'.'
+ QUOTENAME (@Object_Name)
+ @SQL_String_WHERE
+ N'
) sqMAT
UNPIVOT
(
column_data FOR column_name IN
(
' + @SQL_String_IN_Column_Name + N'
)
) UNPV
GROUP BY
UNPV.column_name
,UNPV.column_data
'
)
BEGIN TRY
INSERT INTO dbo.#temp_string_search_results
(
[schema_name]
,[object_name]
,column_name
,column_data
,occurrences
)
EXECUTE (@SQL_String_Full)
END TRY
BEGIN CATCH
END CATCH
SELECT TOP (1)
@Loop_Object_Number = X.schema_object_dense_rank
,@Schema_Name = X.[schema_name]
,@Object_Name = X.[object_name]
FROM
dbo.#temp_string_search_objects_columns X
WHERE
X.schema_object_dense_rank = @Loop_Object_Number + 1
ORDER BY
X.schema_object_dense_rank
IF @@ROWCOUNT = 0
BEGIN
SET @Loop_Object_Number = NULL
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Final Display / Output
-----------------------------------------------------------------------------------------------------------------------------
SELECT
(CASE Y.object_type
WHEN 'U' THEN 'TABLE'
WHEN 'V' THEN 'VIEW'
ELSE 'ERROR'
END) AS object_type
,UPPER (Y.data_type) AS data_type
,Y.max_length AS data_length
,DB_NAME (DB_ID (SUBSTRING (@Database_Name, 2, LEN (@Database_Name) - 2))) AS database_name
,Y.[schema_name]
,Y.[object_name]
,Y.column_name
,Z.column_data
,CONVERT (XML, (CASE Y.data_type
WHEN N'XML' THEN Z.column_data
ELSE N''
END)) AS column_data_xml
,Z.occurrences
FROM
dbo.#temp_string_search_objects_columns Y
INNER JOIN dbo.#temp_string_search_results Z ON Z.[schema_name] = Y.[schema_name]
AND Z.[object_name] = Y.[object_name]
AND Z.column_name = Y.column_name
ORDER BY
Y.[schema_name]
,Y.[object_name]
,Y.column_name
,Z.column_data
-----------------------------------------------------------------------------------------------------------------------------
-- Cleanup: Drop Any Remaining Temp Tables
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_objects_columns', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_objects_columns
END
IF OBJECT_ID (N'tempdb.dbo.#temp_string_search_results', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_string_search_results
END
GO
Subscribe