用游标实现查询当前服务器所有数据库所有表的SQL

最新推荐文章于 2024-08-08 08:00:00 发布

转载最新推荐文章于 2024-08-08 08:00:00 发布 · 198 阅读

文章标签：

本文介绍如何使用SQL游标遍历多个数据库实例，并执行特定查询，包括使用`FOR`循环、`OPEN`、`FETCH`、`WHILE`循环、`CLOSE`和`DEALLOCATE`语句。通过示例展示了如何筛选数据库名称、执行特定数据库操作及使用游标进行数据遍历。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

declare @name varchar(100)
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT Name FROM Master..SysDatabases
where name NOT IN ('master','model','msdb','tempdb') ) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @name   
WHILE @@FETCH_STATUS = 0
BEGIN

if exists(select 1 from  sysobjects where xtype='u' and name like '%qq%')
select name from sysobjects where xtype='u' and name like '%qq%'
FETCH NEXT FROM My_Cursor INTO @name 
END

CLOSE MY_CURSOR
DEALLOCATE My_Cursor

　　其中QQ可以替换成自己想要的条件哦

PS:有问题改了下还是不行，有知道的人吗？

declare @name varchar(100)
declare @sql varchar(100)
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT Name FROM Master..SysDatabases
where name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') ) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @name   
WHILE @@FETCH_STATUS = 0
BEGIN

select @sql='use '+@name
EXEC sp_sqlexec @Sql
--select @name from Master..SysDatabases
--if exists(select 1 from  sysobjects where xtype='u' )--
select * from sysobjects where xtype='u'-- and name like '%sys%'
FETCH NEXT FROM My_Cursor INTO @name 
END


CLOSE MY_CURSOR
DEALLOCATE My_Cursor

DECLARE @dbname VARCHAR(50);
DECLARE @i INT =1; 
DECLARE @maxid INT=0;
CREATE TABLE #temp(
	id int Identity(1,1),
	dbname varchar(50)
)
DECLARE @sql VARCHAR(max)='';
INSERT INTO #temp
        (dbname )
SELECT NAME  FROM master.sys.SysDatabases where name NOT IN ('master','model','msdb','tempdb')
SELECT @maxid=MAX(id) FROM #temp
WHILE @i<@maxid
BEGIN
	DECLARE @db VARCHAR(50)='';
	SELECT @db=dbname FROM #temp WHERE id=@i
	PRINT @db
	IF @i<>1 AND  @i<>@maxid
	BEGIN
		SET @sql+=' UNION ALL'
	END
	SET @sql+=' SELECT * FROM '+@db+'.INFORMATION_SCHEMA.TABLES ' 
	SET  @i+=1;
END
DROP TABLE #temp
EXEC(@sql)

　　经过千辛万苦，找到了方法，正确的方法如下：

sp_MSForEachDB @command1='USE ?;
 
SELECT o.name AS tablename,c.name AS columnname,t.name AS datatype, *
  FROM sys.[columns] AS c
INNER JOIN sys.objects AS o ON c.[object_id]=c.[object_id]
INNER JOIN sys.types AS t ON t.user_type_id=c.user_type_id
WHERE o.[type]=''U'''