December 20, 2017 at 9:21 am
I have to import the counts from table into an Excel and I am wondering if someone knows how to add union in between lines.select s.name+'.'+t.NAME, 'select count (*) from ' +s.NAME+'.'+t.NAME
FROM
sys.tables t
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
order by s.name, t.name
December 20, 2017 at 9:23 am
I am not even sure if union works in this situation...
December 20, 2017 at 9:27 am
Do you need the exact row counts or can a very close approximate do, if so I would join back to sys.partitions where the index id is 0 or 1 and pull the row count out via that metadata view instead. The documentation states the values are "approximate" but I have yet to see a difference when querying select count(*) vs the view
December 20, 2017 at 9:31 am
Yes. The UNION operator eliminates duplicates and hence needs to perform a sort operation. If you know there are no duplicates (which I think there aren't, in this case) you can use UNION ALL to avoid the sort and thus, we hope, improve performance.
Edit - hmmm.... I wrote this before you removed the code that you posted in your second post.
John
December 20, 2017 at 10:02 am
anthony.green - Wednesday, December 20, 2017 9:27 AMDo you need the exact row counts or can a very close approximate do, if so I would join back to sys.partitions where the index id is 0 or 1 and pull the row count out via that metadata view instead. The documentation states the values are "approximate" but I have yet to see a difference when querying select count(*) vs the view
Got the from Uncle Google and just tweaked it a little bit DECLARE @TableRowCounts TABLE ([databaseName] Varchar(100),[SchemaName] VARCHAR(128),[TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([databaseNAme],[SchemaName],[TableName], [RowCount])
EXEC sp_MSforeachdb 'SELECT ''?'',s.Name,TBL.name, SUM(PART.rows) AS rows
FROM sys.tables TBL
INNER JOIN sys.schemas s on TBL.schema_id=s.schema_id
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE IDX.index_id < 2
GROUP BY TBL.object_id, s.Name, TBL.name;' ;
Select SchemaName+'.'+TableName as TableName, [RowCount]
From @TableRowCounts
where databaseName = 'LPDB_API'
order by SchemaName, TableName
December 20, 2017 at 10:11 am
John Mitchell-245523 - Wednesday, December 20, 2017 9:31 AMEdit - hmmm.... I wrote this before you removed the code that you posted in your second post.John
Yes I did remove the code
I had this select s.name+'.'+t.NAME, 'select count (*) from ' +s.NAME+'.'+t.NAME
' UNION'
FROM
sys.tables t
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
order by s.name, t.name
December 20, 2017 at 11:12 am
If you're trying to generate a dynamic SQL command to return rowcounts for every table, this would work.DECLARE @sql NVARCHAR(MAX) = STUFF((
SELECT CONCAT(' UNION ALL SELECT TableName = ''', TableName, ''', [RowCount] = COUNT(*) FROM ', TableName)
FROM (
SELECT TableName = CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name))
FROM sys.tables t
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
) TableList
ORDER BY TableName
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'), 1, 11, '');
EXEC (@sql);
Another way to do this without dynamic SQL is to get the table rowcounts from sys.partitions.SELECT TableName = CONCAT(OBJECT_SCHEMA_NAME(object_id), '.', OBJECT_NAME(object_id)), [RowCount] = SUM(rows)
FROM sys.partitions
WHERE index_id < 2 AND object_id IN (SELECT object_id FROM sys.tables)
GROUP BY OBJECT_ID
ORDER BY TableName
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply