sql - 在动态 SQL 中使用 USE DATABASE_NAME 时不会更改数据库

Question

我可以使用以下查询检索单个数据库

Select Table_catalog[Instance Name],
Table_name[Database Table],
Column_name[Column],
Data_type[Column Type] 
FROM information_schema.columns
where Data_type in ('varchar')
Order by Table_name,Data_type

但我想列出服务器中所有可用数据库的列表

  Declare @I int =1,@Qry nVarchar(1000),@DatabaseName nvarchar(100)
   Declare @TempTable Table
(
 Sno Int Identity(1,1),
  DatabaseName Varchar(100)
 )
    Insert into @TempTable
    Select name 
    from sys.databases
    where database_id>4
 Select * from @TempTable
    While(@i<=(Select max(sno) from @TempTable))
        Begin

            Select @DatabaseName=DatabaseName from @TempTable where sno=@i

                Select @DatabaseName
             set  @Qry='Use '+ @DatabaseName
             **exec sp_executesql @Qry**
             set  @Qry= '
                        Select  Table_catalog[Instance Name],
                                Table_name[Database Table],
                                Column_name[Column],
                                Data_type[Column Type] 
                                FROM information_schema.columns
                        where Data_type in (''datetime'',''date'',''time'',''smalldatetime'')
                        Order by Table_name,Data_type'
                 exec sp_executesql @Qry


        Set @i=@i+1
        End

它不起作用，因为使用数据库名称实际上并未更改数据库并重复当前正在使用的数据库的结果集..对此有任何建议，

Answer 1

动态 SQL 始终在其自己的范围内执行，因此您的 USE 语句对第二个动态查询没有影响。

这应该对你有用(你的循环是不必要的而且很尴尬):

declare @Database sysname, @sql nvarchar(max)
declare Databases cursor local fast_forward
for select name from sys.databases where database_id > 4

open Databases
fetch next from Databases into @Database
while @@fetch_status = 0
begin
    set @sql =  'Select  
                    Table_catalog [Instance Name],
                    Table_name [Database Table],
                    Column_name [Column],
                    Data_type [Column Type] 
                FROM 
                    ' + quotename(@Database) + '.information_schema.columns
                where 
                    Data_type in (''datetime'',''date'',''time'',''smalldatetime'')
                Order by 
                    Table_name,
                    Data_type'

    exec sp_executesql @sql

    fetch next from Databases into @Database    
end
close Databases
deallocate Databases