November 3, 2014 at 6:29 pm
Hi,
I am working in Sqlserver 2008 R2. I am trying to export the stored procedure script using BCP command and am able to that.
I followed the below link, working great.
http://www.kodyaz.com/t-sql/bcp-and-sp_helptext-to-script-store-procedures-into-seperate-files.aspx
Is there any way to export with script with Db name. for example,
use TestDB
procedure script
on the above link, it only export the script without the use statement. How can i export the script along with use statement using bcp command. any suggestions please
November 3, 2014 at 6:42 pm
Quick thought, you can use something like the relevant part in this script
😎
SELECT
DB_NAME() AS DATABASE_NAME
,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA
,OBJECT_NAME(ASM.object_id) AS OBJ_NAME
,N'USE ' + DB_NAME() + NCHAR(59) + NCHAR(13) + NCHAR(10) + ASM.definition AS OBJ_DEFINITION
FROM sys.all_sql_modules ASM
WHERE OBJECTPROPERTY(ASM.object_id,'IsProcedure') = 1
AND ASM.object_id > 0;
November 3, 2014 at 6:52 pm
Hi Erik,
Thanks for your reply.
I am little confused how to apply your logic in my sample. Below is my sample procedure
CREATE PROCEDURE [dbo].[GenerateScriptForStoredProcedure] (
@storedprocedure_name sysname
)
AS
DECLARE @sphelptext TABLE(script NVARCHAR(MAX));
INSERT INTO @sphelptext EXEC sp_helptext @storedprocedure_name;
SELECT script FROM @sphelptext;
GO
USE sample
GO
CREATE PROCEDURE Export_Procedure (@Path VARCHAR(100))
AS
BEGIN
DECLARE @storedprocedure_name VARCHAR(100)
DECLARE @cmd VARCHAR(4000);
DECLARE spCursor CURSOR FAST_FORWARD
FOR
SELECT [ProcName]
FROM [dbo].[ExportProcedures]
OPEN spCursor
FETCH NEXT
FROM spCursor
INTO @storedprocedure_name
WHILE @@fetch_status = 0
BEGIN
SET @cmd = 'bcp "exec [' + DB_NAME() + '].dbo.GenerateScriptForStoredProcedure ' + @storedprocedure_name + '" queryout "' + @Path + @storedprocedure_name + '.sql" -c -UTF8 -T -S' + @@servername
--PRINT @cmd
EXEC master..xp_cmdshell @cmd
FETCH NEXT
FROM spCursor
INTO @storedprocedure_name
END
CLOSE spCursor
DEALLOCATE spCursor
END
I have table called "ExportProcedures" and i will have the procedure names which needs to be exported. Can you please help me on adding your logic in my sample. really my mind is blank how to add your changes.
Thanks.
November 3, 2014 at 7:14 pm
Hi Erik,
Here is my try
ALTER PROCEDURE [dbo].[GenerateScriptForStoredProcedure] (
@storedprocedure_name sysname
)
AS
DECLARE @sphelptext TABLE(script NVARCHAR(MAX));
INSERT INTO @sphelptext
SELECT N'USE ' + DB_NAME() + NCHAR(59) + NCHAR(13) + NCHAR(10) + ASM.definition
FROM sys.all_sql_modules ASM
WHERE OBJECTPROPERTY(ASM.object_id,'IsProcedure') = 1
AND ASM.object_id = (select object_id from sys.objects where name = @storedprocedure_name);
It's working. Do you have any suggestions on this logic?
November 4, 2014 at 9:59 am
Looks good to me, you might want to construct the filename in the same procedure, i.e.
DB_NAME() + NCHAR(44) + OBJECT_SCHEMA_NAME(ASM.object_id) + NCHAR(44) + OBJECT_NAME(ASM.object_id) + N'.sql'
😎
November 5, 2014 at 12:50 pm
wonderful thanks you so much for the heads up
December 4, 2014 at 7:36 pm
Hi Erik,
as i don't want to open a new post for this, as we have all the information on this post, i would like to extend this conversation.
I am able to export the proc perfectly using BCP. Is there any way to add drop statement in the proc before the create statement and at the end can we get the current grant privilege of the proc given
Ex :
drop procedure sample;
Create procedure sample
GRANT EXEC ON sample TO PUBLIC
How can i add the drop at the beginning and Grant at the end when the procedure gets exported. Is it possible to do? any sample please
December 4, 2014 at 10:39 pm
KGJ-Dev (12/4/2014)
Hi Erik,as i don't want to open a new post for this, as we have all the information on this post, i would like to extend this conversation.
I am able to export the proc perfectly using BCP. Is there any way to add drop statement in the proc before the create statement and at the end can we get the current grant privilege of the proc given
Ex :
drop procedure sample;
Create procedure sample
GRANT EXEC ON sample TO PUBLIC
How can i add the drop at the beginning and Grant at the end when the procedure gets exported. Is it possible to do? any sample please
Quick pointers, the code snips below should get you passed this hurdle
😎
/* LISTING PERMISSIONS ON AN OBJECT */
--sys.database_permissions
SELECT
SP.[class]
,SP.[class_desc]
,SP.[major_id]
,SP.[minor_id]
,SP.[grantee_principal_id]
,SP.[grantor_principal_id]
,SP.[type]
,SP.[permission_name]
,SP.[state]
,SP.[state_desc]
FROM sys.database_permissions SP
--WHERE SP.major_id = ASM.object_id
;
--sp_helprotect
DECLARE @PERMITS TABLE
(
[Owner] SYSNAME
,[Object] SYSNAME
,[Grantee] SYSNAME
,[Grantor] SYSNAME
,[ProtectType]SYSNAME
,[Action] SYSNAME
,[Column] SYSNAME
);
INSERT INTO @PERMITS ([Owner],[Object],[Grantee],[Grantor],[ProtectType],[Action],[Column])
EXEC sp_helprotect;
SELECT
P.[Owner]
,P.[Object]
,P.[Grantee]
,P.[Grantor]
,P.[ProtectType]
,P.[Action]
,P.[Column]
FROM @PERMITS P
--WHERE P.[Object] = OBJECT_NAME(ASM.object_id)
/* DROP STATEMENT */
SELECT
CASE
WHEN OBJECTPROPERTY(ASM.object_id, 'IsProcedure') = 1 THEN
N'DROP PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(ASM.object_id)) + NCHAR(46) + QUOTENAME(OBJECT_NAME(ASM.object_id)) + NCHAR(59)
END
,*
FROM sys.all_sql_modules ASM;
December 9, 2014 at 8:25 pm
Hi Erik,
I apologize for the delay in my response. I was on vocation and returning to my work today. I almost nearing my requirement completion.
I am trying to add if exists statement to check the proc exits before deletion. below is my query, but i a missing the quote some where. really couldn't figure out where am i doing mistake. any clue please
declare @storedprocedure_name nvarchar(30);
SELECT + N'USE ' + DB_NAME() + NCHAR(59) + NCHAR(13) + NCHAR(10) +
N'IF EXISTS (SELECT * FROM sysobjects WHERE type = P AND name = '+ @storedprocedure_name ')
BEGIN
DROP PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(ASM.object_id)) + NCHAR(46) + QUOTENAME(OBJECT_NAME(ASM.object_id)) + NCHAR(59)
'END' +
CASE
WHEN OBJECTPROPERTY(ASM.object_id, 'IsProcedure') = 1 THEN
N'DROP PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(ASM.object_id)) + NCHAR(46) + QUOTENAME(OBJECT_NAME(ASM.object_id)) + NCHAR(59)
END
+ NCHAR(13) + NCHAR(10) + ASM.definition
FROM sys.all_sql_modules ASM
WHERE OBJECTPROPERTY(ASM.object_id,'IsProcedure') = 1
AND ASM.object_id = (select object_id from sys.objects where name = @storedprocedure_name);
December 10, 2014 at 3:44 pm
Try this for a size
😎
declare @storedprocedure_name nvarchar(30) = 'my_proc_name';
SELECT N'USE ' + DB_NAME() + NCHAR(59) + NCHAR(13) + NCHAR(10) +
N'IF EXISTS (SELECT * FROM sysobjects WHERE type = P AND name = '+ @storedprocedure_name + N')
BEGIN
DROP PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(ASM.object_id)) + NCHAR(46) + QUOTENAME(OBJECT_NAME(ASM.object_id)) + NCHAR(59) + N'
END
GO
' +
CASE
WHEN OBJECTPROPERTY(ASM.object_id, N'IsProcedure') = 1 THEN
N'
DROP PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(ASM.object_id)) + NCHAR(46) + QUOTENAME(OBJECT_NAME(ASM.object_id)) + NCHAR(59)+ N'
GO
'
END
+ NCHAR(13) + NCHAR(10) + ASM.definition
FROM sys.all_sql_modules ASM
WHERE OBJECTPROPERTY(ASM.object_id,N'IsProcedure') = 1
AND ASM.object_id = (select object_id from sys.objects where name = @storedprocedure_name);
December 10, 2014 at 7:13 pm
You are the man!!!!. Thank you so much.
December 15, 2014 at 5:06 am
@KGJ
Hand-rolling your own code is a great way to learn, but have you looked at the SQL Toolbelt from Redgate. It allows you to do all that stuff to create separate scripts for each proc (in fact every database object: schema, table,index, view, proc, function, synonym etc...) You can also integrate Mgmt Studio with TFS, Subversion or Mercurial for source code control and run schema and data compares between databases and create automatic migration scripts which include the drop commands where necessary and will also script the insert of reference data if necessary. It is not free (but you can get a free 2 week trial) but is worth every penny in my opinion.
If you have more than two developers working on a database it will save you from so many mistakes and makes real the possibility of source code control for the database into the same Version Control System as the applications it supports.
December 20, 2014 at 2:31 pm
Hi Aaron,
thanks for your suggestion. My project was build two year ago and i am new to this project. So it was not implemented till i came to this project. So at least the script i am preparing will help to pull the current script as backup before executing the new version of the script.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply