August 8, 2012 at 12:06 pm
I am trying to write a stored procedure that cursors through all databases and cursors through each table and prints out dynamic sql to compress tables if not compressed. I wrote it in the master database but it only seems to use the master database. So say there are 3 databases and 2 tables in the master not compressed. It will print the code for those 2 tables 3 times.
My code is as follows
DECLARE @DB_NAME VARCHAR(200)
DECLARE @SQL2 VARCHAR(MAX)
DECLARE DB_CUR1 CURSOR FOR
SELECT [NAME] FROM dbo.sysdatabases
WHERE (dbid > 4)
AND STATUS <> 66056 -- OFFLINE DATABASES
ORDER BY dbid
OPEN DB_CUR1
FETCH NEXT FROM DB_CUR1 INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL2 = 'USE ' + @DB_NAME + ';'
EXECUTE(@SQL2)
DECLARE @DB_OBJECT_ID INT
DECLARE @DB_TABLE_NAME VARCHAR(100)
DECLARE @SQL1VARCHAR(MAX)
--Declare currsor to get the Table and Index information
DECLARE DB_CUR CURSOR FOR
SELECT DISTINCT
D.object_id,
D.NAME AS TABLE_NAME
FROM @DB_NAME.sys.all_OBJECTS D, @DB_NAME.sys.partitions p
WHERE D.object_id = P.object_id
AND TYPE = 'U'
and data_compression = 0
ORDER BY D.NAME
OPEN DB_CUR
FETCH NEXT FROM DB_CUR INTO
@DB_OBJECT_ID, @DB_TABLE_NAME
--***********************************************************************
-- LOOP TABLES
--***********************************************************************
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL1 = 'ALTER TABLE ' + @DB_TABLE_NAME + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
print @SQL1
--EXECUTE(@SQL1)
FETCH NEXT FROM DB_CUR INTO
@DB_OBJECT_ID,
@DB_TABLE_NAME
END -- DB_CUR Cursor
CLOSE DB_CUR;
DEALLOCATE DB_CUR;
FETCH NEXT FROM DB_CUR1 INTO @DB_NAME
END -- DB_CUR1 Cursor
CLOSE DB_CUR1;
DEALLOCATE DB_CUR1;
August 8, 2012 at 1:00 pm
I took the liberty of using an online sql formatter to make this a bit easier to read:
DECLARE @DB_NAME VARCHAR(200)
DECLARE @SQL2 VARCHAR(MAX)
DECLARE DB_CUR1 CURSOR
FOR
SELECT [NAME]
FROM dbo.sysdatabases
WHERE (dbid > 4)
AND STATUS <> 66056 -- OFFLINE DATABASES
ORDER BY dbid
OPEN DB_CUR1
FETCH NEXT
FROM DB_CUR1
INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL2 = 'USE ' + @DB_NAME + ';'
EXECUTE (@SQL2)
DECLARE @DB_OBJECT_ID INT
DECLARE @DB_TABLE_NAME VARCHAR(100)
DECLARE @SQL1 VARCHAR(MAX)
--Declare currsor to get the Table and Index information
DECLARE DB_CUR CURSOR
FOR
SELECT DISTINCT D.object_id
,D.NAME AS TABLE_NAME
FROM @DB_NAME.sys.all_OBJECTS D
,@DB_NAME.sys.partitions p
WHERE D.object_id = P.object_id
AND TYPE = 'U'
AND data_compression = 0
ORDER BY D.NAME
OPEN DB_CUR
FETCH NEXT
FROM DB_CUR
INTO @DB_OBJECT_ID
,@DB_TABLE_NAME
--***********************************************************************
-- LOOP TABLES
--***********************************************************************
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL1 = 'ALTER TABLE ' + @DB_TABLE_NAME + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
PRINT @SQL1
--EXECUTE(@SQL1)
FETCH NEXT
FROM DB_CUR
INTO @DB_OBJECT_ID
,@DB_TABLE_NAME
END -- DB_CUR Cursor
CLOSE DB_CUR;
DEALLOCATE DB_CUR;
FETCH NEXT
FROM DB_CUR1
INTO @DB_NAME
END -- DB_CUR1 Cursor
CLOSE DB_CUR1;
DEALLOCATE DB_CUR1;
Now the code as you posted will not work. In fact, it won't compile. You need use either dynamic sql (my preference) or use the undocumented sp_msforeachdb and sp_msforeachtable procedures. There some issues with the procedures though that causes some databases to be skipped in some circumstances. They are officially undocumented and unsupported. With some testing though they can be used in many situations.
I actually think your outer cursor should be fine because all you are really doing there is pulling the database names and sticking the value in @DB_NAME. It is in the inner cursor where you need to use dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2012 at 1:22 pm
Thank you. Pushing the whole inner loop into dynamic sql seems to have worked.
August 8, 2012 at 1:37 pm
You're welcome. The problem is you can't use variables in object names like you were doing. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply