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.
Author |
Topic |
doco
Yak Posting Veteran
77 Posts |
Posted - 2008-02-18 : 10:20:31
|
I often write script for four other entities that use the same databases with the difference being the names of the databases across the four entities. Each entity has two different databases.EG. First database: Prefix[entity_name]Common Second database: Prefix[entity_name]MainThe only thing that changes in these two databases across entities is the [entity_name]. It is quite common to refer to both databases in script; and it is quite common for an individual script to become hundreds of lines with scores of references to the databases.Yes, it is fairly simple to use a text editor to replace all database names with pertinent names. What I am curious is there a way to use a variable to identify the databases in one place in the beginning of the script? Concatenation of @database_name + '..table_name' throws an error.TIAEducation is what you have after you've forgotten everything you learned in school |
|
321dutch
Starting Member
2 Posts |
Posted - 2008-02-18 : 11:04:33
|
You must use dynamic sql in order to use a variable for either a db or column name. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-18 : 11:09:09
|
This can be done by means of dynamic sql. use a variable to store dbname name and use this variable to built sql string likeSELECT @Sql ='SELECT fields FROM ' + @dbname +'.Table'then use EXEC() or sp_executesql to execute the sql string. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-18 : 11:33:09
|
In case if you cant avoid dynamic sql www.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
doco
Yak Posting Veteran
77 Posts |
Posted - 2008-02-18 : 16:28:27
|
Thanks guys.I am very new to TSQL but have a programming background. It just seems a 'reasonable' thing to be able to do, I guess not. Education is what you have after you've forgotten everything you learned in school |
|
|
|
|
|