Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- This isn't my code, found online somewhere in a Google search, forgot to save the link...
- DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
- SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
- SET @FullRowResult = 1
- SET @FullRowResultRows = 3
- SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
- SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
- SET @SearchStrInXML = 0 /* Searching XML data may be slow */
- IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
- CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))
- SET NOCOUNT ON
- DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
- SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
- DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
- WHILE @TableName IS NOT NULL
- BEGIN
- SET @TableName =
- (
- SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_TYPE = 'BASE TABLE'
- AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
- AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
- AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
- )
- IF @TableName IS NOT NULL
- BEGIN
- DECLARE @sql VARCHAR(MAX)
- SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
- FROM INFORMATION_SCHEMA.COLUMNS
- WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
- AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
- AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
- AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
- INSERT INTO @ColumnNameTable
- EXEC (@sql)
- WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
- BEGIN
- PRINT @ColumnName
- SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
- SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
- WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
- ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
- FROM ' + @TableName + ' (NOLOCK) ' +
- ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
- WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
- ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
- INSERT INTO #Results
- EXEC(@sql)
- IF @@ROWCOUNT > 0 IF @FullRowResult = 1
- BEGIN
- SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
- ' FROM ' + @TableName + ' (NOLOCK) ' +
- ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
- WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
- ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
- EXEC(@sql)
- END
- DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
- END
- END
- END
- SET NOCOUNT OFF
- SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
- GROUP BY TableName, ColumnName, ColumnValue, ColumnType
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement