SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 To get the db names as parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arthiasha
Starting Member

India
40 Posts

Posted - 02/11/2013 :  05:58:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 02/11/2013 :  10:35:46  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000