Report Performance

Question

Report Performance

sharonsql2013

SSCertifiable

Points: 5908
More actions
January 28, 2015 at 10:38 am

#296951

I am trying to create a report which always gives a notification if the tables are Empty.
Here is a Query I have that points to the status of one single table.
How can I get it for multiple tables.
DECLARE @ErrorMsg nvarchar(400)
IF (SELECT count(*) FROM dbo.Department_EC ) = 0
BEGIN
SET @ErrorMsg = 'Table is returning nothing'
SELECT @ErrorMsg Err
RETURN
END
Else IF (SELECT count(*) FROM dbo.Department_EC) > = 1
BEGIN
SET @ErrorMsg = 'You are returning Data'
SELECT @ErrorMsg Err
RETURN
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

you can query all tables in the entire database, and determine which have zero rows by taking advantage of the dmvs related to indexes and partition stats

with that, you could also limit ti to just specific tables as well.

/*--Results

ObjectNameTheCount

CarePatient0

PayrollExportMailContents0

*/

SELECT o.name AS ObjectName,

ps.row_count AS TheCount

FROM sys.indexes AS i

INNER JOIN sys.objects AS o

ON i.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats AS ps

ON i.OBJECT_ID = ps.OBJECT_ID

AND i.index_id = ps.index_id

WHERE i.index_id < 2

AND o.is_ms_shipped = 0

AND ps.row_count = 0 --zero rows!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

Well, I don't have permissions to do something like this, Is there a way I can pass multiple table names like in the first query...

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Maybe something like this (using Jeff Moden's community string splitter DelimitedSplit8K)?

DECLARE @ListOfTables VARCHAR(8000) = 'Table1,Table2'

,@SQL NVARCHAR(MAX);

SELECT @SQL=s

FROM

(

SELECT ';SELECT Err=CASE (SELECT COUNT(*) FROM dbo.' + item + ') WHEN 0 THEN ''Table is returning nothing'' ELSE ''You are returning Data'' END'

FROM dbo.DelimitedSplit8K(@ListOfTables, ',')

ORDER BY ItemNumber

FOR XML PATH('')

) a(s);

EXEC sp_executesql @SQL;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 4

CREATE TABLE #Temp

(

TableName VARCHAR(500),

CountOf INT

)

INSERT #Temp

EXEC sp_msForEachTable

'SELECT PARSENAME(''?'', 1),

COUNT(*) FROM ? WITH (NOLOCK)'

SELECT TableName , CountOf

FROM #Temp

DROP TABLE #Temp