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

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='unitid' and a.type='U'

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