Search all tables, all columns for a specific value SQL Server
Rédigé par Sozezzo - - Aucun commentaireThis script* search for a specific value that you can select sql type:
- String - %my string%
- Number - exact number
- Date - exact format yyyy/MM/dd HH:mm:ss
- XML - %my string%
You can limit how many result by tables using "TOP n"
You must select database to run this script, and avoid using production environment.
* Tested with Sql Server 2012, 2014 and 2016
-- Set Parameters ------------ DECLARE @Search nvarchar(120) = '2008'; DECLARE @CheckString INT = 1; DECLARE @CheckNumber INT = 0; DECLARE @CheckDateTime INT = 0; -- cast(@castDateTime) DECLARE @CheckXml INT = 0; DECLARE @ReturnTop NVARCHAR(50) = 'TOP 100'; DECLARE @castDateTime NVARCHAR(50) = 'yyyy/MM/dd HH:mm:ss'; -- yyyy/MM/dd HH:mm:ss ----------------------- SET NOCOUNT ON PRINT '-- Server Name : ' + @@servername PRINT '-- Database : ' + db_name() BEGIN TRY DROP TABLE #ColumnCast; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH; CREATE TABLE #Columncast ( [Typelist] nvarchar(MAX) , [Columncast] nvarchar(128) , [ColumnWhere] nvarchar(256) ); IF (@CheckString = 1) BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|nvarchar|varchar|char|nchar|', '$ColumnName', ' $ColumnCast LIKE ''%$Search%''' ) END; IF (@CheckNumber = 1) BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|int|smallint|real|bigint|bigint|tinyint|float|bit|decimal|numeric|', 'CAST($ColumnName AS NVARCHAR(128))', ' $ColumnCast = ''$Search''' ) END; IF (@CheckDateTime = 1) BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|datetime|time|smalldatetime|', 'FORMAT($ColumnName, '''+@castDateTime+''')', ' $ColumnCast = ''$Search''' ) END; IF (@CheckXml = 1) BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|xml|', 'CAST($ColumnName AS NVARCHAR(MAX))', ' $ColumnCast LIKE ''%$Search%''' ) END; BEGIN TRY DROP TABLE #Tables; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH; CREATE TABLE #Tables ( TableId int IDENTITY(1,1) NOT NULL, TableName nvarchar(512) , ColumnName nvarchar(256) , ColumnWhere nvarchar(256) , ColumnCast nvarchar(512), SqlScript nvarchar(max) ); DECLARE @Template nvarchar(max) = 'SELECT $ReturnTop ''$TableName'' as TableName, ''$ColumnName'' as ColumnName, $Columncast as ColumnValue FROM $TableName with(nolock) WHERE $ColumnWhere'; DECLARE @CollateDatabase nvarchar(100); -- Fix Collation bug SELECT @CollateDatabase = collation_name FROM sys.databases where [name] = db_name(); --SELECT QUOTENAME(s.name) + '.' + QUOTENAME(o.Name) AS TableName , QUOTENAME(c.Name) AS ColumnName , p.ColumnCast , p.ColumnWhere FROM sys.columns c JOIN sys.tables o ON o.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.systypes AS t ON c.system_type_id = t.xtype JOIN (SELECT ColumnCast , [TypeList] , ColumnWhere FROM #ColumnCast ) AS p ON CHARINDEX('|'+t.name COLLATE SQL_Latin1_General_CP1_CI_AI +'|', p.[TypeList]) > 0; DECLARE @TableTemplate nvarchar(max) = 'SELECT QUOTENAME(s.name) + ''.'' + QUOTENAME(o.Name) AS TableName , QUOTENAME(c.Name) AS ColumnName , p.ColumnCast , p.ColumnWhere FROM sys.columns c JOIN sys.tables o ON o.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.systypes AS t ON c.system_type_id = t.xtype JOIN (SELECT ColumnCast , [TypeList] , ColumnWhere FROM #ColumnCast ) AS p ON CHARINDEX(''|''+t.name COLLATE ' + @CollateDatabase + ' +''|'', p.[TypeList]) > 0' PRINT @TableTemplate INSERT INTO #Tables ( TableName, ColumnName, ColumnCast, ColumnWhere ) EXEC (@TableTemplate) UPDATE #Tables SET SqlScript = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Template, '$TableName', TableName),'$ColumnWhere', ColumnWhere),'$Columncast',ColumnCast),'$ColumnName', ColumnName), '$Search', @Search),'$ReturnTop', @ReturnTop); -- select * from #Tables; BEGIN TRY DROP TABLE #Results; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH; CREATE TABLE #Results (ResultId int IDENTITY(1,1) NOT NULL, TableName nvarchar(256) , ColumnName nvarchar(128) , ColumnValue nvarchar(MAX), SqlScript nvarchar(MAX) ); DECLARE @TableName nvarchar(256) , @ColumnName nvarchar(128) , @SqlScript nvarchar(MAX) , @TableId int,@SqlScriptCode NVARCHAR(max), @LastResultId INT; WHILE (exists(select * from #Tables)) BEGIN SELECT @ColumnName = '', @TableName = '', @SqlScript = ''; SELECT TOP 1 @TableId = TableId , @TableName = TableName , @ColumnName = ColumnName , @SqlScript = SqlScript FROM #Tables; DELETE FROM #Tables WHERE TableId = @TableId; SET @SqlScriptCode = REPLACE(@SqlScript, '''', ''''''); SET @SqlScript = REPLACE(@sqlScript , '$SqlScriptCode', @SqlScriptCode); print @SqlScript INSERT INTO #Results (TableName, ColumnName, ColumnValue) EXEC (@SqlScript); SET @SqlScript = REPLACE (@SqlScript, ' FROM', ', * FROM'); UPDATE #Results SET SqlScript = @SqlScript WHERE SqlScript IS NULL; END SELECT DISTINCT @@servername as [Servername], db_name() as DatabaseName, TableName, ColumnName, ColumnValue, SqlScript FROM #Results; DROP TABLE #Results; go
Source: (Old versions) http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated/
Garbage : https://goo.gl/iDHkk