sql server - USE DATABASE_NAME doesn't change the database when used in dynamic SQL -

i able retrieve single database using below query

select table_catalog[instance name], table_name[database table], column_name[column], data_type[column type]  information_schema.columns data_type in ('varchar') order table_name,data_type

but wanted list available databases in server

  declare @i int =1,@qry nvarchar(1000),@databasename nvarchar(100)    declare @temptable table (  sno int identity(1,1),   databasename varchar(100)  )     insert @temptable     select name      sys.databases     database_id>4  select * @temptable     while(@i<=(select max(sno) @temptable))         begin              select @databasename=databasename @temptable 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]                                  information_schema.columns                         data_type in (''datetime'',''date'',''time'',''smalldatetime'')                         order table_name,data_type'                  exec sp_executesql @qry           set @i=@i+1         end 

its not working since use databasename not changing database , repeat result set of database in use .. suggestions on this,

you can use task undocumented procedure sp_msforeachdb. tested on enviroment @ works flawlessly.

edit

as pondlife pointed out, database part missing on 3 part name syntax. added , correctly working. added where clause avoid searching on unnecessary databases master, msdb, tempdb , model.

exec sp_msforeachdb   'select table_catalog[instance name],          table_name[database table],          column_name[column],          data_type[column type]      ?.information_schema.columns   data_type in (''varchar'')         , ''?'' not in (''master'',''msdb'',''tempdb'',''model'')   order table_name,data_type' 

just in case want know more undocumented procedure check here , here. remember undocumented means officially microsoft not support procedure , therefore change without notice.

sample result adventureworks2008r2 database:

 instance name        database table        column              column type adventureworks2008r2    customer          accountnumber            varchar adventureworks2008r2    password          passwordhash             varchar adventureworks2008r2    password          passwordsalt             varchar adventureworks2008r2    salesorderheader  creditcardapprovalcode   varchar