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?

up vote 1 down vote accepted

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)
  • Thank you very much for the above, Kin. With some modifications I was able to get the script to return everything that I wanted / needed. – Tom_W Dec 5 at 11:19

I have a slightly different approach for this type of dynamic select.

First, create all of the SELECT statements you are looking to do, and put them into a temp table / temp table variable

CREATE TABLE #SQL_COMMANDS (
    ID      INT IDENTITY(1,1),
    SQLCmd  NVARCHAR(500)   
)

INSERT INTO #SQL_COMMANDS (SQLcmd)
SELECT 'SELECT schema, Count(*)
FROM [Dynamic].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WITH (NOLOCK) WHERE DATE IS NULL'
FROM [Dynamic].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN ('Tasks', 'Task_NEW', 'Tasks_NEW')
ORDER BY TABLE_SCHEMA

Then, concatenate all of the commands into one string, placing UNION or UNION ALL between each command, like this:

DECLARE @FullSQL NVARCHAR(MAX)

SELECT @FullSQL = IsNull(@FullSQL + '
UNION ALL
', '' ) + SQLCmd 
FROM #SQL_COMMANDS

Finally, run the command:

EXEC (@FullSQL)

This removes cursors and other looping constructs from your SQL.

  • Hi Vergil, thanks for the above. I marked the other answer as accepted, but wanted to thank you too for the concatenate command you posted. I ended up using this in conjunction with Kin's post, and it works perfectly for me. – Tom_W Dec 5 at 11:20

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.