how to create sp in all database.

Question

1.00/5 (1 vote)

See more:

SQL

I have one sp i want to deploy it in all database except system databases..How can create like that..please help me out

Posted 30-Jul-15 22:47pm

Member 11337367

Add a Solution

Comments

stibee 31-Jul-15 4:58am

What do you have?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Answer 1 · 2015-07-30T23:48:00

Info: If you create this stored procedure in master database, it will be available for all the database.

If you need the stored procedure to be created in all databases except master then follow these link which describes step by step to do this.
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example[^]

Now, as (if) you need more system databases to be excluded then, make the changes accordingly in the script (in if condition).

Hope, it helps :)

Wendelius · Answer 2 · 2015-07-31T22:05:00

Solution 2

I really don't advice to create a stored procedure in any of the system databases. In my opinion system databases should be left as-is and they should only be used by the SQL Server (=system).

Having that said, you can create the procedure in any database you want and then call it from other databases using 3-part notation.

For example create this procedure into some database

SQL

create procedure proc1 as
begin
   print('I am here');
end;

and from another database call it using the following command. Just change the name of the database

SQL

execute databse_name..proc1

Of course the procedure operates in the database it's located so if you need data from the calling database, you need either to provide it to the procedure using sufficient parameters or code the 'connection' to another database inside the procedure.

Addition:

If you want to create the procedure in multiple databases (except system databases) you can use a cursor to query the databases, switch to proper database and then create the procedure in it.

Since the SQL statement containing the procedure creation must be the first statement inside a batch, the creation must be done 'dynamically' after the USE statement.

Consider the following example:

SQL

DECLARE @databasename nvarchar(200)
DECLARE @sql nvarchar(max)
DECLARE curDatabases CURSOR FOR 
SELECT name
FROM sys.databases 
WHERE owner_sid <> 1; -- use relevant condition to include only desired databases

OPEN curDatabases

FETCH NEXT FROM curDatabases INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @databasename
	SET  @sql = 'use ' + @databasename + '
exec (''create procedure dbo.proc1 as
begin
   print(''''I am here'''');
end'')'
	EXEC sp_executesql @stmt = @sql

    FETCH NEXT FROM curDatabases INTO @databasename
END 
CLOSE curDatabases;
DEALLOCATE curDatabases;

Another way would be to use sp_msforeachdb[^] and in the SQL statement include a condition not to run the create procedure if the database is a system database.

Posted 31-Jul-15 22:05pm

Wendelius

Updated 1-Aug-15 2:33am

v3

Comments

Member 11337367 1-Aug-15 7:53am

if i want to create in multiple database how..?

Wendelius 1-Aug-15 8:30am

Have a look at the modified answer.

Member 11337367 1-Aug-15 8:58am

I have two doubt
please u tell me what is the use of sp_executesql command and how u could take @stmt without declaring them.

How to write stored procedure in double quote..

Wendelius 1-Aug-15 9:03am

sp_executesql is used to execute a dynamic sql from a string. @stmt is the name of the parameter. See https://msdn.microsoft.com/en-us/library/ms188001.aspx[^]

The two apostrophes are used to escape a single apostrophe. For example the result of

PRINT 'A''B'

would be

A'B

Member 11337367 1-Aug-15 9:01am

One more is there...

DECLARE @databasename nvarchar(200)
DECLARE @sql nvarchar(max)
DECLARE curDatabases CURSOR FOR
SELECT name
FROM sys.databases
WHERE owner_sid <> 1; -- use relevant condition to include only desired databases

OPEN curDatabases

FETCH NEXT FROM curDatabases INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @databasename
SET @sql = 'use ' + @databasename + '
exec (''alter proc getmonthwiseusercount
@year varchar(50),
@month varchar(50),
@filepath varchar(56)
as
begin
declare @sql varchar(max)
declare @ch char(2)
set @ch=CHAR(13)+CHAR(10)
declare @header varchar(max)
declare @tempheader varchar(500)
declare @tempdetail varchar(500)

set @tempheader=@filepath+'header.txt'
set @tempdetail=@filepath+'detail.txt'

set @header='select ''''empcode'''' as empcode,''''fsname'''' as fsname,
''''ntype'''' as ntype,''''desg'''' as desg,''''userid'''' as userid,''''dcr_count'''' dcrcount
'
set @header='exec master..xp_cmdshell ''bcp "'+@header+'" queryout "'+@tempheader+'" -S -d apex_gmc -T -c '''
set @header=REPLACE(@header,@ch,'')
exec (@header)

set @sql='select f.C_EmpNo as EmpCode,f.C_Name as FSName,ua.N_Type as NType,ua.c_sh_name as Desg,
ui.C_UserID as UserID,isnull(dwr.dcrcount,0) as DCR_Count
from
Tbl_FS_Mst f
join Tbl_User_Access ua on f.N_Type=ua.N_Type
join Tbl_User_Info ui on f.C_EmpNo=ui.C_Code
left join
(
select c_fs_code,COUNT(distinct n_srno) as dcrcount
from tbl_dwr d
where MONTH(d.D_Date_Report)='+convert(varchar,@month)+' and YEAR(d.D_Date_Report)='+convert(varchar,@year)+'
group by c_fs_code
)dwr on dwr.C_FS_Code=f.C_Code
where f.C_EmpNo<>''''000000'''' or dwr.dcrcount is not null

order by f.C_EmpNo,f.N_Type,f.C_Name
'

set @sql='master..xp_cmdshell ''bcp "'+ @sql +'" queryout "'+@tempdetail+'" -S -d apex_gmc -T -c'''
set @sql=REPLACE(@sql,@ch,' ')
exec(@sql)

declare @mergedata varchar(max)
declare @filename varchar(500)
set @filename='D:apexusercount.xls'

set @mergedata='master..xp_cmdshell ''copy '+@tempheader+' + '+@tempdetail+' '+@filename+''''

exec (@mergedata)

declare @delfiles varchar(500)

set @delfiles='exec master..xp_cmdshell ''del '+@tempheader+''''

exec (@delfiles)

set @delfiles='exec master..xp_cmdshell ''del '+@tempdetail+''''

exec (@delfiles)
end

end''
)'
EXEC sp_executesql @stmt = @sql

FETCH NEXT FROM curDatabases INTO @databasename
END
CLOSE curDatabases;
DEALLOCATE curDatabases;

ia getting error for this

error is lik in below

Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'header'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '''.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'bcp'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near '000000'.
Msg 102, Level 15, State 1, Line 71
Incorrect syntax near 'del'.
Msg 102, Level 15, State 1, Line 75
Incorrect syntax near 'del'.

Wendelius 1-Aug-15 9:05am

You need to escape those single quotes as I explained in previous post

Member 11337367 1-Aug-15 9:27am

I have to remove like this..for ex:
befor
set @tempheader=@filepath+'header.txt'
set @tempdetail=@filepath+'detail.txt'

after
set @tempheader=@filepath+header.txt
set @tempdetail=@filepath+detail.txt

Wendelius 1-Aug-15 10:27am

Try
set @tempheader=@filepath+'''header.txt'''

Member 11337367 3-Aug-15 4:19am

it is not working..:(

Wendelius 11-Aug-15 23:42pm

Not sure if you have already fixed this but if not, what is the error you get?