Hi All,
I am putting together a series of TSQL statements to be used as part of a DB maintenance plan on SQL server 2005 on Windows Server 2003. I know that the statement itself works against a single database. The intention is to write one routine that will execute on all non-system DB's. Here are the system specs:
__
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324
1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
__
So I've actually tried two methods. The first is without using sp_MSforeachdb
__
-- Declare variables
Declare @.dbname sysname,
@.cmd varchar(4096)
-- Select DB Names
Select @.dbname = min(name) from master.dbo.sysdatabases
-- Loop through each database.
WHILE @.dbname is not null -- <-- (NEED TO NOT INCLUDE SYSTEM DB'S HERE)
Begin
Select @.cmd = 'use ' + @.dbname +
'
-- Delete data from table1 and related tables older than 2 Months
DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2
DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1)
DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1)
DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1)
-- Delete Selective Data from table2 older than 6 Months
DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in
('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)
--
-- Delete Data From table3 older than 2 Years
DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2
-- Shrink all Non System DB's
DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)
'
End -- (NOT SURE IF THIS IS IN THE RIGHT PLACE)
-- Execute the command
Exec (@.cmd)
__
The second attempted method was trying to use sp_MSforeachdb. I modified the following to successfully loop a simple print command to ensure that the looping occurs:
__
declare @.cmd1 varchar(4096)
set @.cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
exec sp_MSforeachdb @.command1=@.cmd1
-- THIS WORKS
__
However after many attempts, I haven't been able to get the syntax right to run my
statements abonve using this method. Seems very unforgiving with spaces and apostrophes.
Any advice is much appreciated. Thank you in advance.
Rick B
Hi Rick,
Try something like this:
--THIS CREATES ONE TEMP TABLE WITH ALL NON SYSTEM DATABASES
SELECT IDENTITY(INT, 1,1) AS ID, DBNAME
INTO TEMP_CURSOR
FROM from master.dbo.sysdatabases
where SID<>0x01;
DECLARE @.ID INT, @.DBNAME VARCHAR(250)
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > 0;
WHILE @.ID IS NOT NULL
BEGIN
SELECT @.DBNAME=DBNAME
FROM TEMP_CURSOR
WHERE ID = @.ID;
EXEC(' USE ' + @.DBNAME + '
-- Delete data from table1 and related tables older than 2 Months
DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;
DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);
-- Delete Selective Data from table2 older than 6 Months
DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in ('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)
--
-- Delete Data From table3 older than 2 Years
DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2
-- Shrink all Non System DB's
DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)
')
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > @.ID;
END
DROP TABLE TEMP_CURSOR;
I thinks this can do your trick
Regards,
|||One of the ways I use to test the syntax of dynamic sql is to print the SQL rather than run it. Then you can see the outputted SQL and see if its correct and if not, where you have gone wrong
hope this helps
|||Hey Luis
Upon execution against Master, I'm receiving the following error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'DBNAME'.
After a few tweaks, the following is the current syntax that I am using based off of Luis's feedback. Can anyone ID additional syntax errors? Thanks in advance. Rick B.
__
SELECT IDENTITY(INT, 1,1) AS ID, DBNAME
INTO TEMP_CURSOR
FROM master.dbo.sysdatabases
where SID<>0x01;
DECLARE @.ID INT, @.DBNAME VARCHAR(250)
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > 0;
WHILE @.ID IS NOT NULL
BEGIN
SELECT @.DBNAME=DBNAME
FROM TEMP_CURSOR
WHERE ID = @.ID;
EXEC(' USE ' + @.DBNAME + '
-- Delete data from table1 and related tables older than 2 Months
DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;
DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);
DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);
-- Delete Selective Data from table2 older than 6 Months
DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in (TableX,TableY,TableZ)
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)
--
-- Delete Data From table3 older than 2 Years
DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2
-- Shrink all Non System DBs
DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)
')
SELECT @.ID = MIN(ID)
FROM TEMP_CURSOR
WHERE ID > @.ID;
END
DROP TABLE TEMP_CURSOR;
__
|||Replace this statement:
SELECT IDENTITY(INT, 1,1) AS ID, DBNAME
INTO TEMP_CURSOR
FROM master.dbo.sysdatabases
where SID<>0x01;
with this one
SELECT IDENTITY(INT, 1,1) AS ID, NAME AS DBNAME
INTO TEMP_CURSOR
FROM master.dbo.sysdatabases
where SID<>0x01;
Hope it works
