Dynamic SQL Query to iterate through all databases to get same value in a column

• shogunSQL

  Say Hey Kid

  Points: 689

  More actions

  August 13, 2018 at 8:55 am

  #360190

  Hi,

  I have three sample databases and tables. They all have same structures but with different naming of databases. See Code below for example.
  
Create Database TestDB10001;
        Create Table TabDocument
    (
    ID int,
    Body nvarchar(50)
    );

  insert into TabDocument
(
ID, Body
)
Values
(
1, 'Word Template'
);

  Create Database TestDB10002;
    Create Table TabDocument
    (
    ID int,
    Body nvarchar(50)
    );

  insert into TabDocument
(
ID, Body
)
Values
(
1, 'Email'
);

  Create Database TestDB10003;

      Create Table TabDocument
    (
    ID int,
    Body nvarchar(50)
    );

  insert into TabDocument
(
ID, Body
)
Values
(
1, 'Excel Template'
);

  Here is how I write my dynamic sql code to get the database name and tables with column Body has the word "Template":

  Declare @SQLStatement nvarchar(max)
Declare @tablename nvarchar(50)
Declare @Schema nvarchar(50)
Declare @databasename nvarchar(50)
Declare @columnname nvarchar(50)

  Set @columnname = 'Body'
Set @databasename = 'TestDB10001'
Set @schema = 'dbo'
Set @tablename = 'TabDocument'

  Set @SQLStatement = 'Select Databasename = ''' + Quotename(@databasename) + ''', [ID], [Body], FROM ' + Quotename(@databasename) + '.' + Quotename(@schema) + '.' + Quotename(@tablename) + 'Where Body like '%Template%''   

  Exec sp_executesql @Sqlstatement


  However, I can only put in one variable at a time for @databasename. How should I pass in all database name in this dynamic sql? Thanks in advance.

  The final result should has three columns:
  DatabaseName and ID and Body
  TestDB10001 | 1 | Word Template
  TestDB10003 | 1 | Excel Template

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 14, 2018 at 7:19 am

  #2001633

  Why does this data need to be in separate databases?   It's much more easily accomplished in just one.   However, given the multiple databases, you should probably at least look at an undocumented stored procedure from Microsoft known as sp_msforeachdb, which you can use to do something once for each database on the server.

  EXEC sp_MSforeachdb 'SELECT ''?'' AS DB_NAME';

  is a simple example.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)