SQL Server: Can you help me with this query?
- by rlb.usa
I want to run a diagnostic report on our MS SQL 2008 database server.
I am looping through all of the databases, and then for each database, I want to look at each table. But, when I go to look at each table (with tbl_cursor), it always picks up the tables in the database 'master'.
I think it's because of my tbl_cursor selection :
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
How do I fix this?
Here's the entire code:
SET NOCOUNT ON
DECLARE @table_count INT
DECLARE @db_cursor VARCHAR(100)
DECLARE database_cursor CURSOR FOR
SELECT name FROM sys.databases where name<>N'master'
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @db_cursor
WHILE @@Fetch_status = 0
BEGIN
PRINT @db_cursor
SET @table_count = 0
DECLARE @table_cursor VARCHAR(100)
DECLARE tbl_cursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN tbl_cursor
FETCH NEXT FROM tbl_cursor INTO @table_cursor
WHILE @@Fetch_status = 0
BEGIN
DECLARE @table_cmd NVARCHAR(255)
SET @table_cmd = N'IF NOT EXISTS( SELECT TOP(1) * FROM ' + @table_cursor + ') PRINT N'' Table ''''' + @table_cursor + ''''' is empty'' '
--PRINT @table_cmd --debug
EXEC sp_executesql @table_cmd
SET @table_count = @table_count + 1
FETCH NEXT FROM tbl_cursor INTO @table_cursor
END
CLOSE tbl_cursor
DEALLOCATE tbl_cursor
PRINT @db_cursor + N' Total Tables : ' + CAST( @table_count as varchar(2) )
PRINT N'' -- print another blank line
SET @table_count = 0
FETCH NEXT FROM database_cursor INTO @db_cursor
END
CLOSE database_cursor
DEALLOCATE database_cursor
SET NOCOUNT OFF
