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'''