SQL根据内容查找表名方法

最新推荐文章于 2023-02-15 11:27:33 发布

IT_0802

最新推荐文章于 2023-02-15 11:27:33 发布

阅读量3k

点赞数 1

分类专栏：随记文章标签： SQL 查找表名

本文链接：https://blog.csdn.net/IT_0802/article/details/82732201

版权

随记专栏收录该内容

25 篇文章 0 订阅

订阅专栏

本文提供两种SQL脚本方法，用于在数据库中查找包含特定字符串的所有表及其对应的字段。第一种适用于SQL Server 2008 R2，通过游标遍历所有字符型字段并检查是否包含指定字符串；第二种为通用过程，创建临时表存储结果。

摘要由CSDN通过智能技术生成

未知原因从这里复制过去的数据库脚本不能在数据库运行，所以有需要的可以通过百度云链接点击下载：

链接：https://pan.baidu.com/s/12emE2cICyq-L8O_iNNRqPw
提取码：266t

第一种：试用SQL Server 2008 r2, 其它的没试过

declare @cloumns varchar(400)
declare @tablename varchar(400)
declare @str varchar(400)
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='select @tmp_counts=count(*) from ' +@tablename+ ' where ' +@cloumns+' = ''' +@str+ ''''
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

第二种:

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