April 1, 2008 at 7:55 am
Hi all - I have a query which when executed against any particular database, will give me a row count for every user table. Simple stuff - I just needed a very quick, developer-over-my-shoulder solution to confirm that a particular user database was completely empty.
So, I was bored and decided I would expand that same query to then dynamically issue the "USE [database]" command and execute the same query as above, so that I could then get row counts for all user tables in all databases, but the query seems to give me row counts over and over again for the same database or two... not sure what's going on.
I'm hoping a fresh pair of eyes can help me out. (And I'm fairly new to TSQL - I'm in training - so don't crucify me for violating any conventions, though I'll take any advice on that as well, certainly) 🙂
The query is:
=================================
DECLARE @dbname VARCHAR(50)
DECLARE @string VARCHAR(2500)
DECLARE db_cursor CURSOR
FOR SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb','pubs','northwind')
ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@fetch_status = 0
BEGIN
SET @string = 'USE ' + @dbname
EXEC @string
SET @string = '
SET NOCOUNT ON
DECLARE @User_Table_Name varchar(200)
DECLARE @string2 VARCHAR(2500)
DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = ''U''
OPEN User_Tables_Cursor
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string2 = ''SELECT COUNT(*) FROM '' + @User_Table_Name
EXEC (@string2)
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END
CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor'
EXEC (@string)
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
=================================
When I change the two bold/red 'EXEC' to 'PRINT', the output seems to look like it's fine, so I'm missing something:
=================================
USE dn2
SET NOCOUNT ON
DECLARE @User_Table_Name varchar(200)
DECLARE @string2 VARCHAR(2500)
DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = 'U'
OPEN User_Tables_Cursor
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string2 = 'SELECT COUNT(*) FROM ' + @User_Table_Name
EXEC (@string2)
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END
CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor
USE Integrity
SET NOCOUNT ON
DECLARE @User_Table_Name varchar(200)
DECLARE @string2 VARCHAR(2500)
DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = 'U'
OPEN User_Tables_Cursor
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string2 = 'SELECT COUNT(*) FROM ' + @User_Table_Name
EXEC (@string2)
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END
CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor
USE IntegrityProd
SET NOCOUNT ON
DECLARE @User_Table_Name varchar(200)
DECLARE @string2 VARCHAR(2500)
DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = 'U'
OPEN User_Tables_Cursor
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string2 = 'SELECT COUNT(*) FROM ' + @User_Table_Name
EXEC (@string2)
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END
CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor
USE IntegrityProd_Log
SET NOCOUNT ON
DECLARE @User_Table_Name varchar(200)
DECLARE @string2 VARCHAR(2500)
DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = 'U'
OPEN User_Tables_Cursor
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string2 = 'SELECT COUNT(*) FROM ' + @User_Table_Name
EXEC (@string2)
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END
CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor
USE master
SET NOCOUNT ON
etc, etc, etc....
=================================
"Got no time for the jibba jabba!"
-B.A. Baracus
April 1, 2008 at 7:57 am
When I change the PRINT back to EXEC, I get the following:
21307
0
21307
0
21307
0
21307
0
etc
etc
so somehow I'm not getting past the first database, even though I see that I am in the PRINT version
"Got no time for the jibba jabba!"
-B.A. Baracus
April 1, 2008 at 8:23 am
I am going to ignore your cursor issue and just point out a couple of other items related to your script.
Rather than using sysobjects, the information_schema.tables view will help avoid version dependency.
Also, here is a neat trick to avoid counting all of the actual tables (which can take awhile).
If your statistics are up-to-date (and in an empty table they almost always are), the rowcnt field in sysindexes on the PK index of a table will be the number of records in the table.
This number is not 100% accurate (depending on statistics being updated), but if you are looking for quick table counts without actually counting tables, this is a good trick.
April 1, 2008 at 8:34 am
I may be missing something, but I don't see where the first query has a loop for the databases in it.
Shouldn't it have "while @@fetch_status = 0 ... fetch next ..." for the databases cursor? Or am I just not seeing it?
Without the loop, it will only execute once.
(Edit: I see the loop now. It's the layout of the code that threw me.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2008 at 8:40 am
Have you tried the new "reports" feature? Right-click on the database, pick Reports, Standard reports, "Disk usage by table". And voila - rowcounts (and much more) by table....
Hey - it's even something your devs can run (without wrecking your database).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 1, 2008 at 8:41 am
The last few lines:
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
It's executing several times, but it's only giving me the row counts from the first database (dn2), which has two tables, hence the output of:
21307
0
21307
0
21307
0
21307
0
etc
etc
"Got no time for the jibba jabba!"
-B.A. Baracus
April 1, 2008 at 8:43 am
Matt Miller (4/1/2008)
Have you tried the new "reports" feature? Right-click on the database, pick Reports, Standard reports, "Disk usage by table". And voila - rowcounts (and much more) by table....Hey - it's even something your devs can run (without wrecking your database).
yeah, and this isn't vital that I get it working... but the ex-programmer in me refuses to give up on a problem that should be simple to solve. 🙂
"Got no time for the jibba jabba!"
-B.A. Baracus
April 1, 2008 at 9:15 am
I got it working. The only thing I changed was:
SET @string = 'USE ' + @dbname
EXEC @string
SET @string = 'blah blah blah'
to
SET @string = 'USE ' + @dbname + 'blah blah blah'
So, removed the EXEC on the USE command and just combine that into the 2nd string, which handles getting the row count from each table.
"Got no time for the jibba jabba!"
-B.A. Baracus
April 1, 2008 at 9:15 am
I modified the script as follows:
create table #Test (
DB varchar(100),
TableName varchar(100),
RowQty int)
DECLARE @dbname VARCHAR(50)
DECLARE @string VARCHAR(2500)
DECLARE db_cursor CURSOR
FOR SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb','pubs','northwind')
ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@fetch_status = 0
BEGIN
SET @string = 'USE ' + @dbname
exec (@string)
SET @string = null
SET NOCOUNT ON
DECLARE @User_Table_Name varchar(200)
DECLARE @string2 VARCHAR(2500)
DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = 'U'
OPEN User_Tables_Cursor
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string2 = 'insert into #test (db, tablename, rowqty) SELECT ''' + @dbname + ''', ''' + @user_table_name + ''', COUNT(*) FROM ' + @User_Table_Name
EXEC (@string2)
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END
CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor
exec (@string)
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #test
Then I tested it, and found that the "Use" command from the first cursor didn't carry over. It went database by database, selecting the counts from the database I ran the whole script in.
So, I modified it to:
drop table #test
create table #Test (
DB varchar(100),
TableName varchar(100),
RowQty int)
DECLARE @dbname VARCHAR(50)
DECLARE @string VARCHAR(2500)
DECLARE db_cursor CURSOR
FOR SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb','pubs','northwind')
ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@fetch_status = 0
BEGIN
SET @string = 'USE ' + @dbname
exec (@string)
SET @string = @string + '
SET NOCOUNT ON
DECLARE @User_Table_Name varchar(200)
DECLARE @string2 VARCHAR(2500)
DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = ''U''
OPEN User_Tables_Cursor
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string2 = ''insert into #test (db, tablename, rowqty) SELECT ''''' + @dbname + ''''', '''''' + @user_table_name + '''''', COUNT(*) FROM '' + @User_Table_Name
EXEC (@string2)
FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END
CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor'
exec (@string)
--print @string
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #test
And it seems to be working. Getting the number of single-quotes right in the @string2 build was tricky.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2008 at 9:21 am
Thanks, that's pretty handy too, since it's not just a list of endless numbers without context. Better to use a temp table like you did, I think... although I don't really expect to have to ever run such a query, but nice practice for me I guess
"Got no time for the jibba jabba!"
-B.A. Baracus
April 1, 2008 at 11:24 am
You're welcome.
Without the temp table, you'd quickly run into the problem of too many selects, which Management Studio will bomb out on.
Of course, it's a really bad idea to run this on a production server. Like you, I just wanted to see if I could get the theory to work. It does work, but man does it eat up server resources and take forever to run.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply