August 21, 2015 at 1:00 am
Hi,
I like to backup the stored procedures' code used in my databases. So I created this Script:
/*
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NOT NULL DROP TABLE [Monitor].[dbo].[Procedurecode];
*/
DECLARE
@db nvarchar(50),
@strSQL nvarchar (100)
IF CURSOR_STATUS('global','cur1')>=-1 BEGIN DEALLOCATE cur1 END
DECLARE cur1 CURSOR FOR
SELECT name FROM sys.databases
WHERE name not in ('Reporter1','Reporter1TempDB','ReportServer','ReportServerTempDB','sysutility_mdw','tempdb')
OPEN cur1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL ='USE ' + @db
EXEC sp_executesql @strSQL
IF CURSOR_STATUS('global','cur2')>=-1 BEGIN DEALLOCATE cur2 END
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NULL CREATE TABLE [Monitor].[dbo].[Procedurecode] (DB nvarchar(128), ROUTINE_NAME varchar(max), ROUTINE_DEFINITION varchar(max), LAST_ALTERED datetime, INSERT_DATE date)
DECLARE
@routine_name varchar(max),
@routine_definition varchar(max),
@last_altered datetime
DECLARE cur2 CURSOR FOR
SELECT ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
OPEN cur2
FETCH NEXT FROM cur2 INTO @routine_name, @routine_definition, @last_altered
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [Monitor].[dbo].[Procedurecode] (DB, ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED, INSERT_DATE)
VALUES (DB_NAME(), @routine_name, @routine_definition, @last_altered, CONVERT (date, GETDATE()))
FETCH NEXT FROM cur2 INTO @routine_name, @routine_definition, @last_altered
END
CLOSE cur2
DEALLOCATE cur2
END
FETCH NEXT FROM cur1 INTO @db
CLOSE cur1
DEALLOCATE cur1
Problem is (and I seem not to be the only one with tis) described here:
"If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running." There is nothing magical in SQL server that knows you intend a series of dynamic sql commands to be part of a single job running in a single context. You have to build the entire string for the job you want to execute."
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/01c64ab0-9227-475a-a59a-ed80f9333042.htm
So I only get the SPs of the current database. Do you have any ideas how to solve this problem anyway?
Thanks in advance!
August 21, 2015 at 1:24 am
ratloser99 36160 (8/21/2015)
Hi,I like to backup the stored procedures' code used in my databases. So I created this Script:
/*
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NOT NULL DROP TABLE [Monitor].[dbo].[Procedurecode];
*/
DECLARE
@db nvarchar(50),
@strSQL nvarchar (100)
IF CURSOR_STATUS('global','cur1')>=-1 BEGIN DEALLOCATE cur1 END
DECLARE cur1 CURSOR FOR
SELECT name FROM sys.databases
WHERE name not in ('Reporter1','Reporter1TempDB','ReportServer','ReportServerTempDB','sysutility_mdw','tempdb')
OPEN cur1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL ='USE ' + @db
EXEC sp_executesql @strSQL
IF CURSOR_STATUS('global','cur2')>=-1 BEGIN DEALLOCATE cur2 END
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NULL CREATE TABLE [Monitor].[dbo].[Procedurecode] (DB nvarchar(128), ROUTINE_NAME varchar(max), ROUTINE_DEFINITION varchar(max), LAST_ALTERED datetime, INSERT_DATE date)
DECLARE
@routine_name varchar(max),
@routine_definition varchar(max),
@last_altered datetime
DECLARE cur2 CURSOR FOR
SELECT ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
OPEN cur2
FETCH NEXT FROM cur2 INTO @routine_name, @routine_definition, @last_altered
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [Monitor].[dbo].[Procedurecode] (DB, ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED, INSERT_DATE)
VALUES (DB_NAME(), @routine_name, @routine_definition, @last_altered, CONVERT (date, GETDATE()))
FETCH NEXT FROM cur2 INTO @routine_name, @routine_definition, @last_altered
END
CLOSE cur2
DEALLOCATE cur2
END
FETCH NEXT FROM cur1 INTO @db
CLOSE cur1
DEALLOCATE cur1
Problem is (and I seem not to be the only one with tis) described here:
"If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running." There is nothing magical in SQL server that knows you intend a series of dynamic sql commands to be part of a single job running in a single context. You have to build the entire string for the job you want to execute."
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/01c64ab0-9227-475a-a59a-ed80f9333042.htm
So I only get the SPs of the current database. Do you have any ideas how to solve this problem anyway?
Thanks in advance!
Flippant answer: use a source control system for your databases.
Less flippant answer: why are you using a cursor for each proc? Do it in one hit, per database! As for the 'Use' comment, all of your subsequent SQL needs to be part of the same statement.
@SQL = 'use db; [more code here]'
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 21, 2015 at 1:55 am
okay, I've feared this. Thank you for your reply.
August 21, 2015 at 2:30 am
OK, and please get rid of cur2:
insert Monitor.dbo.Procedurecode
(DB
,ROUTINE_NAME
,ROUTINE_DEFINITION
,LAST_ALTERED
,INSERT_DATE
)
select db_name()
,ROUTINE_NAME
,ROUTINE_DEFINITION
,LAST_ALTERED
,convert (date, getdate())
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 21, 2015 at 2:47 am
Oh, thanks!
August 21, 2015 at 7:12 am
Now it works! Definition code in information_schema.routines ist varchar(4000), the definition code might be incomplete. So I needed to use another source.
If you change @var to 'V', you can backup all your views, with 'TF' you might backup functions, too.
DECLARE @db nvarchar(20)
DECLARE @strSQL nvarchar (max)
DECLARE @table nvarchar(50) = 'Monitor.dbo.Code_Procedures'
DECLARE @var varchar(3) = 'P'
DECLARE @strDEL nvarchar(max)
DECLARE @STR nvarchar (max) = 'IF CURSOR_STATUS(''global'',''cur2'')>=-1 BEGIN DEALLOCATE cur2 END
--IF OBJECT_ID('''+ @table + ''') IS NOT NULL DROP TABLE '+ @table +'
IF OBJECT_ID('''+ @table + ''') IS NULL CREATE TABLE '+ @table + '(
[DB_NAME] nvarchar(128),
[SCHEMA_NAME] nvarchar(128),
ROUTINE_NAME varchar(max),
ROUTINE_DEFINITION varchar(max),
CREATE_DATE datetime,
LAST_ALTERED datetime,
INSERT_DATE date)
DECLARE
@db_name nvarchar(128),
@schema_name nvarchar(128),
@routine_name varchar(max),
@routine_definition varchar(max),
@create_date datetime,
@last_altered datetime
DECLARE cur2 CURSOR FOR
SELECT
DB_NAME(),
s.name,
o.name,
m.definition,
o.create_date,
o.modify_date
FROM sys.objects o
INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type = '''+ @var +'''
OPEN cur2
FETCH NEXT FROM cur2 INTO
@db_name,
@schema_name,
@routine_name,
@routine_definition,
@create_date,
@last_altered
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO '+ @table + ' (
[DB_NAME],
[SCHEMA_NAME],
ROUTINE_NAME,
ROUTINE_DEFINITION,
CREATE_DATE,
LAST_ALTERED,
INSERT_DATE)
VALUES (
@db_name,
@schema_name,
@routine_name,
@routine_definition,
@create_date,
@last_altered,
CONVERT (date, GETDATE()))
FETCH NEXT FROM cur2 INTO
@db_name,
@schema_name,
@routine_name,
@routine_definition,
@create_date,
@last_altered
END
CLOSE cur2
DEALLOCATE cur2'
IF CURSOR_STATUS('global','cur1')>=-1 BEGIN DEALLOCATE cur1 END
DECLARE cur1 CURSOR FOR
SELECT name FROM sys.databases
WHERE name not in ('Reporter1','Reporter1TempDB','ReportServer','ReportServerTempDB','sysutility_mdw','tempdb','master','model','msdb')
OPEN cur1
FETCH NEXT FROM cur1 INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = 'USE ' + @db +'; ' + @STR
EXEC sp_executesql @strSQL
FETCH NEXT FROM cur1 INTO @db
END
CLOSE cur1
DEALLOCATE cur1
/*
SET @strDEL = 'DELETE FROM ' + @table + ' WHERE [INSERT_DATE] < CONVERT (date, GETDATE()-30)'
EXEC sp_executesql @strDEL
*/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply