Sql-server – Dynamic SQL – How to you use it to run a SELECT against all schema’s and Tables

dynamic-sqlsql servert-sql

Scenario

I have a database that contains all of our customer data, which is separated by
Schemas. For example:

Customer1 = Schema1, 
Customer2 = Schema2, 
Customer3 = Schema3, 
etc...

Sometimes we get an issue where a Date value in a table is sometimes NULL. This table is contained in every Schema, but can sometimes be called 'Tasks', 'Tasks_NEW' or 'Task_NEW' (got to love consistency).

I am looking for a way that I can dynamically run a SELECT statement that will return a count of all NULL Date values, across all Tables and all Schemas.

So far, I have done this in order to put the Schema, plus the Table into a TempTable;

CREATE TABLE #TASK_TABLES
(   ID      INT IDENTITY(1,1),
    TenantID        INT,
    TableName       NVARCHAR(255)   )

INSERT INTO #TASK_TABLES
SELECT TABLE_SCHEMA, TABLE_NAME
FROM [Dynamic].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN ('Tasks', 'Task_NEW', 'Tasks_NEW')
ORDER BY TABLE_SCHEMA

Now that I have this information, I want to cycle through it with a SELECT like this;

SELECT Schema, COUNT(Date)
FROM [Dynamic].[Schema].[Table] (NOLOCK)
WHERE DATE IS NULL

Sadly, I don't fully know how to write Dynamic SQL as I'm still learning, but I was hoping that someone might be able to point me into the right direction?

Best Answer

Below is self explanatory code. Alternatively you can use sp_ineachdb (written by Aaron Bertrand).

 set nocount on
  -- create temp table .. 
 create table #commandsToRun (currentDB sysname, schemaName sysname, TableName sysname, columnName sysname, datatype sysname, commandToRun nvarchar(max))
 insert into #commandsToRun
SELECT   
        db_name() as CurrentDB, SCHEMA_NAME(tbl.schema_id) as SchemaName, tbl.name AS TableName,
         c.name AS ColumnName, tp.name as DataType, 
         N'select count('+c.name+') as TotalDateColumnAsNULL, '
                    + ''''+SCHEMA_NAME(tbl.schema_id)+'''' + ' as  SchemaName, '
                    + ''''+tbl.name + ''''+ ' AS TableName' + ''
                    +' from '+ db_name() +'.' + SCHEMA_NAME(tbl.schema_id) 
                    +'.'+ tbl.name +' with (nolock) where '
                    +c.name+' is NULL'as CommandToRun
FROM
  sys.columns c
JOIN sys.tables tbl ON
  c.object_id = tbl.object_id
JOIN sys.types tp on c.user_type_id = tp.user_type_id
WHERE
  ( c.name = 'Date' -- filter the column that you want to check 
  or tp.name = 'datetime' -- filter the datatype if you dont know the column names 
  )
-- check the contents of the table 
select * from #commandsToRun

-- generate exec() to run the sql generated in the CommandToRun

declare @sqltext nvarchar(max) = N''

select  @sqltext += N'exec ('''+replace(CommandToRun,'''','''''')+'''); '
from #commandsToRun

-- uncomment below to run the sql generated.
-- exec(@sqltext)

Related Solutions

Sql-server – How to use same stored procedure for different tables and avoid using dynamic sql

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause.

CREATE VIEW tablesviews AS
SELECT columns, 'TABLE1' as tablename
FROM Table1
UNION ALL
SELECT columns, 'TABLE2' as tablename
FROM Table2
...


SELECT {some columns}
FROM  tableviews
WHERE Tablename=@table and [other where clauses]

Sql-server – Run Query Against All DB’s on Server

OK, replacing the previous answer with this one. USE OF ms_foreach stored procedures is not recommended. This is a little more code but it's more reliable. You can limit the databases you search through, it checks to make sure the transaction table exists, and still provides the database name in the results. Note - this will provide a separate result set for each DB. If this is not desired, create a temp table and dump the results into the temp table by uncommenting the table creation block for #Results, the INSERT just before "EXEC sp_executesql @runMe" and the final SELECT.

    DECLARE 
          @DataSetID INT
        , @Linkdate VARCHAR(19)
        , @runMe NVARCHAR(4000)
        , @dbCnt INT
        , @dbName VARCHAR(500)
        , @tableName VARCHAR(500)
        , @objName VARCHAR(500)

    --initialize variables
    SET @DataSetID = 1;
    SET @Linkdate = '8/31/2015';
    SET @tableName = 'transactions'; --assuming if this is present the other are as well
    SET @dbName='';

    /*
    --CREATE A TABLE TO CAPTURE ALL RESULT
    IF OBJECT_ID('tempdb..#Results','U') IS NOT NULL
        DROP TABLE #Results;

    CREATE TABLE #Results
    (
          DBName            VARCHAR(500)
        , InterfaceName     VARCHAR(255)
        , BrokerCode        VARCHAR(255)
        , BrokerDescription VARCHAR(1000)
    )
    */


    IF OBJECT_ID('tempdb..#DBList','U') IS NOT NULL
        DROP TABLE #DBList;

    SELECT name 
    INTO #DBList
    FROM sys.databases;

    SELECT @dbCnt=COUNT(1)
    FROM #DBList;

    WHILE @dbCnt > 0
    BEGIN
        SELECT @dbName=MIN(name)
        FROM #DBList
        WHERE name > @dbName

        SET @objName = @dbName+'..'+@tableName

        IF OBJECT_ID(@objName) IS NOT NULL
        BEGIN
            SET @runMe = N'
            select distinct '''+@dbName+''' AS DBName, I.InterfaceName
                ,case 
                    when B.brokerCode is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
                ,case 
                    when B.brokerDescription is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
            from ['+@dbname+']..transactions T
            inner join ['+@dbname+']..interfaceoriginations I on I.OriginationID = t.OriginationID
            left join ['+@dbname+']..brokers B on B.BrokerID = t.BrokerID
            where linkdate = '''+@linkdate+'''
            order by 1
                ,2;'

            --INSERT #Results
            EXEC sp_executesql @runMe
        END

        SET @dbCnt = @dbCnt-1
    END

    --SELECT * FROM #Results

Best Answer

Related Solutions

Sql-server – How to use same stored procedure for different tables and avoid using dynamic sql

Sql-server – Run Query Against All DB’s on Server

Related Question