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?