用游标实现查询当前服务器所有数据库所有表的SQL
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'''
![](https://pic.cnblogs.com/avatar/567748/20200111202800.png)
作者:
KMSFan
出处:http://www.cnblogs.com/kmsfan
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
欢迎大家加入KMSFan之家,以及访问我的优酷空间!
出处:http://www.cnblogs.com/kmsfan
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
欢迎大家加入KMSFan之家,以及访问我的优酷空间!