SQL根据关键字（字段名称或值）查找所在的表

# --数据查找（值） #
--调用 exec _FindValueInDB '关键字'

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

CREATE PROCEDURE [dbo].[_FindValueInDB] (@value VARCHAR(1024)) AS BEGIN SET NOCOUNT ON; DECLARE @sql VARCHAR(1024) DECLARE @table VARCHAR(64) DECLARE @column VARCHAR(64) CREATE TABLE #t (tablename VARCHAR(64), columnname VARCHAR(64)) DECLARE TABLES CURSOR FOR SELECT o.name, c.name FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) ORDER BY o.name, c.name OPEN TABLES FETCH NEXT FROM TABLES INTO @table, @column WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] ' SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') ' SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', ''' SET @sql = @sql + @column + ''')' EXEC(@sql) FETCH NEXT FROM TABLES INTO @table, @column END CLOSE TABLES DEALLOCATE TABLES SELECT * FROM #t DROP TABLE #t End

　　

# --根据列名找表名 #

?

1 2 3

select a.name [表名],b.name [列名] from sysobjects a,syscolumns b where a.id=b.id and b.name='关键字' and a.type='U'

　　

# --统计表数据行数 #

?

1 2 3

select t.name as [TableName],i.rows as [RowsCount] from sys.tables as t, sysindexes as i where t.object_id = i.id and i.indid <=1