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

Question

I am able to retrieve for a single database using the below query

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

but I wanted to list it for all available databases in the server

  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

Its not working since the use databasename is not actually changing the database and repeat the result set of the database currently in use .. any suggestions on this,

Your two queries will run on different scope (that's something EXCECUTE sp_executesql does) so the "USE" statement has no effect on the second EXCECUTE. You should just use schema prefix in your statement. — Serge
– Serge, Commented May 8, 2013 at 12:03

Pondlife · Accepted Answer · 2013-05-06 18:48:50Z

2

Dynamic SQL always executes in its own scope, so your USE statement has no effect on the second dynamic query.

This should work for you (your loop is unnecessary and awkward):

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

answered May 6, 2013 at 18:48

Pondlife

16.3k6 gold badges40 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AakashM Over a year ago

Just yesterday I read The case against INFORMATION_SCHEMA views which you might find interesting. For SQL Server, the sys. catalog views are much to be preferred.

Community · Accepted Answer · 2020-07-29 18:37:10Z

1

You can use for this task the undocumented procedure sp_MSforeachdb. Just tested on my enviroment at it works flawlessly.

EDIT

As Pondlife pointed out, the database part was missing on the three part name syntax. Added and now is correctly working. Also added a WHERE clause to avoid searching on unnecessary databases as master, msdb, tempdb and model.

EXEC sp_MSforeachdb 
 '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'')
        AND ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')
  ORDER BY Table_name,Data_type'

Just in case you want to know more about this undocumented procedure check here and here. Remember that undocumented means that officially Microsoft does not support this procedure and therefore it could change without notice.

Sample result from 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

edited Jul 29, 2020 at 18:37

CommunityBot

11 silver badge

answered May 6, 2013 at 8:20

Yaroslav

6,55210 gold badges54 silver badges91 bronze badges

4 Comments

Arun Kumar Over a year ago

tanx This one it gets executed the no of times the COUnt of database available i.e I have 5 databases the query gets executed 5 times

Yaroslav Over a year ago

Yes, that is exactly what it does. And for each database it runs the query you need to execute to return a list of all columns wich have data type varchar on all tables on all databases. Upvote or check as correct answer so others know this is the right answer to your question.

Pondlife Over a year ago

@Yaroslav, I think that Aran is saying that your query returns exactly the same results, once per database. That's because you forgot to put the database name placeholder in the code: FROM ?.information_schema.columns.

Yaroslav Over a year ago

Yes, you are right @Pondlife, I'm editing my answer so it returns back the correct values. Just double checked with AdventureWorks and other databases with different data types, is ok now.

Arun Kumar · Accepted Answer · 2013-05-08 12:00:07Z

Thanks @pondlife as per your suggestion i could make small changes( pass the database name from outside the scope of dynamic query) and the query returns the result instead cursors i have used while loop

      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'+quotename(@DatabaseName)+ '. 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

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related