To get the db names as parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

To get the db names as parameters

Author

Topic

arthiasha
Starting Member

40 Posts

Posted - 2013-02-11 : 05:58:06

Hi all,

I have a scenario like i have to create the same database for our different clients. The clients name is the db name. So i need to generate a .bat script to call the parameters and write a stored procedure to update the db name as clients name in the table_schema and stored procedure.
So when i run the script and manually enter the client id alone, the db should be created with the given name and as client id is the unique id it should get updated in all the tables.

I have the location of the db files like table_schema, stored_procedure etc in D:\db
Please help with sample script or template

thanks in advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-11 : 10:35:46

I think you would have to convert the SQL statements where you have client name into dynamic SQL statements and execute them to achieve this. For example, if your create statement had client name in it, you would change it to use a parameter like shown below.

DECLARE @clientName NVARCHAR(64) = 'Google';
DECLARE @sql NVARCHAR(4000);

SET @sql =
'CREATE DATABASE '+QUOTENAME(@clientName)+' ON  PRIMARY 
( NAME = N'''+@clientName+ ''', FILENAME = N''F:\Data\' + @clientName  + '.mdf'' , SIZE = 3072KB , FILEGROWTH = 1024KB ) 
LOG ON 
( NAME = N'''+@clientName+ '_log'', FILENAME = N''G:\Logs\' + @clientName  + '_log.ldf'' , SIZE = 1024KB , FILEGROWTH = 10%)';

exec sp_executesql @sql;

If these databases are on separate servers, it would be advisable to use the same database name. That will make maintenance, access, coding etc. a lot simpler.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources