My lovely developers like to chanllenge my scripting skills. Today I was asked to restore one database on Dev server by using the backup taken from Prod. However, they wanted to keep all the store process in Dev. In order to avoid the store procs were removed during the dateabase restoring by prod backup, I had to script them out and then run them after restoring completed. The following codes help me to do that in one go without repeating the same process.
SET NOCOUNT ON
DECLARE @SP TABLE ( Id INT IDENTITY (1, 1),
Code VARCHAR (MAX) );
INSERT INTO @SP (Code)
SELECT 'IF OBJECT_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL ' + char(13) + char(10)
+ ' DROP PROCEDURE [' + schema_name(schema_id) + ' ].[' + Name + ']' + char(13) + char(10)
+ ' GO' + char(13) + char(10)
+ OBJECT_DEFINITION(OBJECT_ID) + char(13) + char(10) + 'GO' + char(13) + char(10)
FROM sys.procedures
WHERE is_ms_shipped = 0;
DECLARE @ID AS INT;
DECLARE @SCRIPTS AS VARCHAR (MAX);
SET @ID = 1;
WHILE @ID <= (SELECT MAX(Id)
FROM @SP)
BEGIN
SELECT @SCRIPTS = Code
FROM @SP
WHERE Id = @ID;
WHILE @SCRIPTS <> ''
BEGIN
PRINT LEFT(@SCRIPTS, 8000);
SET @SCRIPTS = SUBSTRING(@SCRIPTS, 8001, LEN(@SCRIPTS));
END
SET @ID = @ID + 1;
END
There is simple query the query to see the definetions (scripts) for all the exsiting objects on the database.
SELECT LEFT(o.name, 100) AS Object_Name,
o.type_desc,
m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
o.type_desc,
m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
No comments:
Post a Comment