考虑有一个名为:People的表
| Id | Name | Code | | 1 | John | 857 | | 2 | Mike | 893 | | 3 | Sara | 935 |
此表位于PeopleDb表中.
问题是 :
我想找到’Mike’关键字.
情况:
我不知道要搜索哪个数据库,表.
我需要一个搜索所有数据库和表的T-sql查询并向我显示:
| Id | DatabaseName | TableName | ColumnName | Pk | SearchValue | 1 | 'PeopleDb' | 'People' | 'Name' | 2 | 'Mike'
我不知道如何编写查询以在所有数据库和表中进行搜索.
任何帮助将不胜感激.
编辑:
速度不是问题,我需要这样做.
我尝试了这个查询,我想要相同但搜索所有数据库.
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright ? 2002 Narayana Vyas Kondreddi.All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT DECLARE @Results TABLE(ColumnName nvarchar(370),ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256),@ColumnName nvarchar(128),@SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ),'IsMSShipped' ) = 0 ) WHILE(@TableName IS NOT NULL) AND(@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName,2) AND TABLE_NAME = PARSENAME(@TableName,1) AND DATA_TYPE IN ('char','varchar','nchar','nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO @Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''',LEFT(' + @ColumnName + ',3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName,ColumnValue FROM @Results END
更新:
我需要一个工作的T-Sql来搜索所有数据库,表,列,所有类型的变量.
当前的答案在nvarchar字段中的连接字符串等某些情况下不起作用.
解决方法
我已根据下面更新了您的逻辑,它工作正常,请看看:
DECLARE @SearchStr VARCHAR(50)='Surat' DECLARE @Results TABLE(DatabaseName NVARCHAR(500),TableName nvarchar(370),ColumnName nvarchar(370),ColumnValue nvarchar(3630),PrimaryKey nvarchar(200),PrimaryKeyValue nvarchar(4000)) SET NOCOUNT ON DECLARE @TableList AS Table ( TableName VARCHAR(500),RowNo INT ) DECLARE @ColumnList AS Table ( ColumnName VARCHAR(500),RowNo INT ) DECLARE @PrimaryKeyList AS Table ( PrimaryKeyName VARCHAR(500) ) DECLARE @TableName nvarchar(256),@SearchStr2 nvarchar(110),@PrimaryKey nvarchar(200),@CurrentTableName nvarchar(256) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') DECLARE @DatabaseCount INT=0,@Index INT=0,@DatabaseName NVARCHAR(500),@TotalColumnCount INT,@ColumnIndex INT=0,@TotalTableCount INT,@TableIndex INT=0 SELECT *,ROW_NUMBER() OVER (ORDER BY name) AS RowNo INTO #tblDatabases FROM Sys.Databases WHERE name NOT IN ('master','model','msdb','tempdb') SELECT @DatabaseCount=COUNT (*) FROM #tblDatabases WHILE @Index<@DatabaseCount BEGIN SET @Index=@Index+1 SELECT @DatabaseName='',@TableIndex=0,@ColumnIndex=0,@TableName='',@ColumnName='' SELECT @DatabaseName=name FROM #tblDatabases WHERE RowNo=@Index DELETE FROM @TableList INSERT INTO @TableList EXEC(' SELECT QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME),ROW_NUMBER() OVER (ORDER BY TABLE_NAME) FROM ['+@DatabaseName+'].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' ') SELECT @TotalTableCount=COUNT(*) FROM @TableList WHILE @TableIndex<@TotalTableCount BEGIN SET @TableIndex=@TableIndex+1 SELECT @ColumnName = '',@ColumnIndex=0 SELECT @TableName=TableName FROM @TableList WHERE RowNo=@TableIndex SET @CurrentTableName=REPLACE(REPLACE(REPLACE(@TableName,'[dbo].',''),'[',']','') DELETE FROM @ColumnList INSERT INTO @ColumnList EXEC('SELECT COLUMN_NAME,ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) FROM ['+@DatabaseName+'].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME('''+@TableName+''',2) AND TABLE_NAME = PARSENAME('''+@TableName+''',1) AND DATA_TYPE IN (''char'',''varchar'',''nchar'',''nvarchar'') ') SELECT @TotalColumnCount=COUNT(*) FROM @ColumnList WHILE @ColumnIndex<@TotalColumnCount BEGIN SET @ColumnIndex=@ColumnIndex+1 SET @ColumnName='' SELECT @ColumnName=ColumnName FROM @ColumnList WHERE RowNo=@ColumnIndex DELETE FROM @PrimaryKeyList INSERT INTO @PrimaryKeyList EXEC(' SELECT Col.Column_Name from ['+@DatabaseName+'].INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,['+@DatabaseName+'].INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = ''PRIMARY KEY'' AND Col.Table_Name= '''+@CurrentTableName+''' ') SELECT @PrimaryKey='' SELECT @PrimaryKey=PrimaryKeyName FROM @PrimaryKeyList SET @PrimaryKey=ISNULL(@PrimaryKey,'') IF @ColumnName IS NOT NULL AND @PrimaryKey<>'' BEGIN INSERT INTO @Results EXEC ( 'SELECT '''+@DatabaseName+''','''+@CurrentTableName+''',''' + @ColumnName + ''',3630),'''+@PrimaryKey+''',[' + @PrimaryKey + '] FROM ['+@DatabaseName+'].' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END END SELECT ROW_NUMBER() OVER (ORDER BY DatabaseName) AS Id,DatabaseName,TableName,ColumnName,ColumnValue AS SearchValue,PrimaryKeyValue AS Pk,PrimaryKey FROM @Results DROP TABLE #tblDatabases