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

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

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

　　

# --根据列名找表名 #

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

　　

# --统计表数据行数 #

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