My goal is to get a list of tables from a stored procedure and then return the name of those tables along with their row count and if a
-- Create temporary table
CREATE TABLE #TempTable
[object_name] NVARCHAR(255) ,
-- Insert table names, object id and row count into temp table
INSERT INTO #TempTable
[object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name ,
JOIN sys.objects o ON d.referenced_id = o.[object_id]
INNER JOIN sys.indexes AS i ON i.object_id = o.object_id
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.object_id = ddps.object_id
AND i.index_id = ddps.index_id
WHERE o.[type] IN ( 'U', 'V' )
AND i.index_id < 2
AND o.is_ms_shipped = 0;
-- Join temp table to query that searches for a ModifiedDate column
SELECT DISTINCT t2.[object_name] AS 'TableName' ,
t2.[row_count] AS 'RowCount' ,
WHEN t1.name ='modifiedDate' THEN 'Yes'
WHEN t1.name <> 'modifiedDate' THEN 'No'
END AS 'ModifiedDateExists'
FROM sys.columns AS t1
RIGHT JOIN #TempTable AS t2 ON t1.object_id = t2.object_id;
-- Delete temp table
IF ( OBJECT_ID('mydbhere..#TempTable') IS NOT NULL )
DROP TABLE #TempTable
The problem is the sys.columns will contain multiple records for 1 object (table) (one for each column). You need a 1-1 relationship there. The distinct your using is hiding the fact that you'd have ALOT more rows (1 for each field infact.) So you need to ensure only 1 record is returned from the join between sys.columns and #TempTable. This can be done by only searching for modifiedDate and allowing the existing of a NULL on your right join to mean that it doesn't exit.
SELECT t2.[object_name] AS 'TableName' , t2.[row_count] AS 'RowCount' , case when t1.name is not null then 'YES' else 'No' as 'ModifiedDateExists' FROM sys.columns AS t1 RIGHT JOIN #TempTable AS t2 ON t1.object_id = t2.object_id and t1.name ='modifiedDate'
Since SQL server will prevent the same named object (column/filed) from being in a table, simply limit to just that column; all other records would result in a Null, and then in our case statement we can say yes or no.
All the tables in #tempTable will be returned but only 1 column.
General rule of thumb is to avoid Distinct unless you REALLY Mean it as it generally is just hiding a problem on a join, limit or something.