SQLTeam.com | Weblogs | Forums

Select data from table based on schema and sys tables query

Imagine a query that is something like this:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],[Tables].name AS [TableName], SUM([Partitions].[rows]) AS [TotalRowCount]
              FROM sys.tables AS [Tables]
              JOIN sys.partitions AS [Partitions]      ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 )
              WHERE SCHEMA_NAME(schema_id) = 'Stage'
              GROUP BY SCHEMA_NAME(schema_id), [Tables].name

This gives me the schema and table name along with how many rows there are in that table.

Cool. Next step is, I want to go through each row or join to another table (Call this table "Entities") that basically has an "IsActive" column to which I want to see for the given scheme and tablename if the data in the "Entities" table has the IsActive set to 1 to filter out the results from the above query.

Any thoughts on how to do this?

Essentially it seems to be to some how do some kind of CTE or join from the above query into the Entities table where IsActive = 0 (in the Entities able) and the TableName (from above) = Entites.TableName

SELECT SCHEMA_NAME(T.schema_id) AS [SchemaName],T.name AS [TableName], 
       SUM(P.[rows]) AS [TotalRowCount]
              FROM sys.tables AS [Tables] T
              JOIN sys.partitions AS [Partitions] P     ON T.[object_id] = P.[object_id] AND 
                     P.index_id IN ( 0, 1 )
              WHERE SCHEMA_NAME(schema_id) = 'Stage' AND
                     EXISTS(SELECT 1 FROM dbo.Entities E WHERE E.SchemaName = SCHEMA_NAME(T.schema_id) AND 
                     E.TableName = T.name AND E.IsActive = 0)
              GROUP BY SCHEMA_NAME(T.schema_id), T.name

Lovely! Thank you.