SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to add union in between lines


How to add union in between lines

Author
Message
NewBornDBA2017
NewBornDBA2017
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3840 Visits: 924
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

NewBornDBA2017
NewBornDBA2017
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3840 Visits: 924
I am not even sure if union works in this situation...
anthony.green
anthony.green
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101370 Visits: 8698
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



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132199 Visits: 19240
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
NewBornDBA2017
NewBornDBA2017
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3840 Visits: 924
anthony.green - Wednesday, December 20, 2017 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

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

NewBornDBA2017
NewBornDBA2017
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3840 Visits: 924
John Mitchell-245523 - Wednesday, December 20, 2017 9:31 AM
Edit - 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




Scott Coleman
Scott Coleman
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23987 Visits: 2138
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




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search