All geek questions in one place

USE DATABASE_NAME does not modify the database when used in dynamic SQL

I can get for one database using the following 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 on 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 using databasename does not actually modify the database and does not repeat the result set of the database used .. any suggestions on this,

+4
source share
3 answers

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

EDIT

As Pondlife pointed out, the database part was missing from the three part name syntax. Added and now works correctly. A WHERE also been added to avoid searching unnecessary databases like 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, if you want to know more about this undocumented procedure, check here and. Remember that undocumented means that Microsoft officially does not support this procedure, and therefore it may change without warning.

Example 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
+1
source

Dynamic SQL is always executed in its area, so your USE statement does not affect the second dynamic query.

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

 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
+2
source

Thanks @pondlife according to your suggestion, I can make small changes (pass the database name from outside the dynamic query), and the query returns the result instead of the cursors that I used in the 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
0
source

Source: https://habr.com/ru/post/1479324/

More articles:


All Articles