sqlserver根据字段值反查所在表和所在的字段。

sqlserver根据字段值反查所在表和所在的字段。非常有用的一段脚本

DECLARE @cloumns VARCHAR(40);

DECLARE @tablename VARCHAR(40);

DECLARE @str VARCHAR(40);

DECLARE @counts INT;

DECLARE @sql NVARCHAR(2000);

DECLARE MyCursor CURSOR FOR

SELECT a.name AS Columns,

b.name AS TableName

FROM syscolumns a,

sysobjects b,

systypes c

WHERE a.id = b.id

AND b.type = 'U'

AND a.xtype = c.xtype

AND c.name LIKE '%char%';

SET @str = '周杰伦';

OPEN MyCursor;

FETCH NEXT FROM MyCursor

INTO @cloumns,

@tablename;

WHILE (@@Fetch_Status = 0)

BEGIN

SET @sql = N'select @tmp_counts=count(*) from ' + @tablename + N' where ' + @cloumns + N' = ''' + @str + N'''';

EXECUTE sp_executesql @sql, N'@tmp_counts int out', @counts OUT;

IF @counts > 0

BEGIN

PRINT '表名为:' + @tablename + ',字段名为' + @cloumns;

END;

FETCH NEXT FROM MyCursor

INTO @cloumns,

@tablename;

END;

CLOSE MyCursor;

DEALLOCATE MyCursor;

sqlserver根据字段值反查所在表和所在的字段。