***********************************字段在哪個表中
select tab.name table_name, col.name column_name
? from sysobjects tab
? left join syscolumns col on tab.id = col.id and tab.xtype = 'U'
?where col.name like '%fkfz10000003%' ?
order by 1,2
*************************************
查詢整個
數據庫
中某個特定值所在的表和字段的方法
?
通過做一個存儲過程，只需要傳入一個想要查找的值，即可查詢出這個值所在的表和字段名。前提是要將這個存儲過程放在所查詢的數據庫。
?
CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
? ? @value VARCHAR(1024)
) ? ? ? ?
AS
BEGIN
? ? -- SET NOCOUNT ON added to prevent extra result sets from
? ? -- interfering with SELECT statements.
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
?
例如，要查詢‘admin’，新建一個查詢輸入
?
EXEC SP_FindValueInDB 'admin'
會返回相應記錄，Tablename顯示被查詢數據所在表，Columnname顯示被查詢數據所在
?
?
?
查看數據庫中的字段所在的表中或某值所在的表和字段
