October 4, 2016 at 12:34 pm
Experts,
Is is possible to create multiple tables with same tablename and column in multiple databases without selecting each database from the drop down menu and execute the script? It works however it is time consuming. I was told to use sp_msforeachdb but no idea how to use it? Any suggestion?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 4, 2016 at 12:41 pm
New Born DBA (10/4/2016)
Experts,Is is possible to create multiple tables with same tablename and column in multiple databases without selecting each database from the drop down menu and execute the script? It works however it is time consuming. I was told to use sp_msforeachdb but no idea how to use it? Any suggestion?
Sure.
Create table DBName.SchemaName.TableName(col1 etc etc)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2016 at 1:10 pm
Thanks for your kind response but there are 35 tables and all these tables have to be created in 150+ DBs. I already generated the script from the QA environment but don't want to execute the script by going to each DB. Is there fastest way to accomplish this task?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 4, 2016 at 1:24 pm
Phil Parkin (10/4/2016)
New Born DBA (10/4/2016)
Experts,Is is possible to create multiple tables with same tablename and column in multiple databases without selecting each database from the drop down menu and execute the script? It works however it is time consuming. I was told to use sp_msforeachdb but no idea how to use it? Any suggestion?
Sure.
Create table DBName.SchemaName.TableName(col1 etc etc)
It looks like Phil got the create table syntax essentially correct but forgot to include the call to sp_MsForEachDB. 😀
Here it is:
exec sp_MsForEachDB
'
USE ?;
Create table DBName.SchemaName.TableName(col1 etc etc)
';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 4, 2016 at 1:24 pm
sp_msforeachdatabase is undocumented and overrated. That said, let's say you wanted to create a table named SomeTable in each db, you could do so like this:
EXEC sp_msforeachdb
'
USE ?;
IF db_name() IN (''AlanMisc'') -- put a list of databases here
CREATE TABLE dbo.SomeTable(id int, col1 int);
';
Alternatively you can use a system table like this:
DECLARE @sql varchar(max) = '';
SELECT @sql += 'CREATE TABLE '+ name + '.dbo.SomeTable(id int, col1 int);'+char(13)+char(10)
FROM sys.databases
WHERE name IN ('') -- your list here;
PRINT @sql;
-- EXEC (@SQL);
-- Itzik Ben-Gan 2001
October 4, 2016 at 1:25 pm
New Born DBA (10/4/2016)
Thanks for your kind response but there are 35 tables and all these tables have to be created in 150+ DBs. I already generated the script from the QA environment but don't want to execute the script by going to each DB. Is there fastest way to accomplish this task?
OK, that would have been useful information.
One way is to use SQLCMD.
Create a single .sql file (eg, CreateTables.sql) which contains all of the CREATE TABLE scripts (do not include database names anywhere in this script).
Create a .cmd file which calls the above .sql file over and over again, once per database. Something like this
sqlcmd -S [servername] -d [DBName1] -i CreateTables.sql
sqlcmd -S [servername] -d [DBName2] -i CreateTables.sql
sqlcmd -S [servername] -d [DBName3] -i CreateTables.sql
(I'd use Excel to build the command strings, rather than typing them all manually.)
Now execute the .cmd file.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2016 at 1:26 pm
Eric M Russell (10/4/2016)
Phil Parkin (10/4/2016)
New Born DBA (10/4/2016)
Experts,Is is possible to create multiple tables with same tablename and column in multiple databases without selecting each database from the drop down menu and execute the script? It works however it is time consuming. I was told to use sp_msforeachdb but no idea how to use it? Any suggestion?
Sure.
Create table DBName.SchemaName.TableName(col1 etc etc)It looks like Phil got the create table syntax essentially correct but forgot to include the call to sp_MsForEachDB. 😀
Here it is:
exec sp_MsForEachDB
'
USE ?;
Create table DBName.SchemaName.TableName(col1 etc etc)
';
Essentially correct? 🙂
If you were using sp_MsForEachDB, you would not include the DBName in the CREATE TABLE command ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2016 at 1:38 pm
You have to be careful with sp_MSforeachdb, because it will also run in the system databases. I assume that you don't really want to create these tables in master, model, msdb, or tempdb.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2016 at 1:41 pm
drew.allen (10/4/2016)
You have to be careful with sp_MSforeachdb, because it will also run in the system databases. I assume that you don't really want to create these tables in master, model, msdb, or tempdb.Drew
That's why, in my example, I include:
IF db_name() IN (''<list of tables here>'')
-- Itzik Ben-Gan 2001
October 4, 2016 at 2:18 pm
Create a proc in the master db, beginning the name with "sp_", mark that proc as a system object, then use sp_MSforeachdb to run the proc in the db(s) you need to.
Here's the shell for that:
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_create_tables
AS
SET NOCOUNT ON;
IF OBJECT_ID('dbo.table_name1') IS NULL
BEGIN
CREATE TABLE table_name1 ...;
CREATE UNIQUE CLUSTERED INDEX ... ON table_name1(...);
END /*IF*/
IF OBJECT_ID('dbo.table_name2') IS NULL
BEGIN
CREATE TABLE table_name2 ...;
...
END /*IF*/
...
GO
EXEC sp_MS_marksystemobject 'dbo.sp_create_tables'
GO
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
RETURN;
/*add additional db name restrictions here, if any*/
/*IF ''?'' ...*/
EXEC [?].dbo.sp_create_tables
'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 4, 2016 at 2:25 pm
sp_MsForEachDB may not be officially documented by Microsoft, but back in 2007 an anonymous member of Microsoft's SQL Server team did obliquely acknowledge it's existence.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 4, 2016 at 2:33 pm
ScottPletcher (10/4/2016)
Create a proc in the master db, beginning the name with "sp_", mark that proc as a system object, then use sp_MSforeachdb to run the proc in the db(s) you need to.Here's the shell for that:
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_create_tables
AS
SET NOCOUNT ON;
IF OBJECT_ID('dbo.table_name1') IS NULL
BEGIN
CREATE TABLE table_name1 ...;
CREATE UNIQUE CLUSTERED INDEX ... ON table_name1(...);
END /*IF*/
IF OBJECT_ID('dbo.table_name2') IS NULL
BEGIN
CREATE TABLE table_name2 ...;
...
END /*IF*/
...
GO
EXEC sp_MS_marksystemobject 'dbo.sp_create_tables'
GO
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
RETURN;
/*add additional db name restrictions here, if any*/
/*IF ''?'' ...*/
EXEC [?].dbo.sp_create_tables
'
Why are you using a RETURN here? Just test for the negative of your original statement.
EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
/*add additional db name restrictions here, if any*/
/* AND ''?'' ...*/
EXEC [?].dbo.sp_create_tables
'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2016 at 3:10 pm
drew.allen (10/4/2016)
ScottPletcher (10/4/2016)
Create a proc in the master db, beginning the name with "sp_", mark that proc as a system object, then use sp_MSforeachdb to run the proc in the db(s) you need to.Here's the shell for that:
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_create_tables
AS
SET NOCOUNT ON;
IF OBJECT_ID('dbo.table_name1') IS NULL
BEGIN
CREATE TABLE table_name1 ...;
CREATE UNIQUE CLUSTERED INDEX ... ON table_name1(...);
END /*IF*/
IF OBJECT_ID('dbo.table_name2') IS NULL
BEGIN
CREATE TABLE table_name2 ...;
...
END /*IF*/
...
GO
EXEC sp_MS_marksystemobject 'dbo.sp_create_tables'
GO
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
RETURN;
/*add additional db name restrictions here, if any*/
/*IF ''?'' ...*/
EXEC [?].dbo.sp_create_tables
'
Why are you using a RETURN here? Just test for the negative of your original statement.
EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
/*add additional db name restrictions here, if any*/
/* AND ''?'' ...*/
EXEC [?].dbo.sp_create_tables
'
Drew
I prefer to just exit when conditions warrant it, rather than having to embed/repeat IF conditions, or get into too many negative conditions.
Quick, true or false: You do not collect $200 when passing GO in Monopoly?
Since the system dbs are almost always skipped, it's cleaner to me to just RETURN. Naturally you're free to adjust that for you.
Also, often there are complex conditions and/or multiple steps to do, and sometimes I want to exit early under certainly conditions. Rather than having to process a continued string of IF conditions -- which I know already to be false -- I prefer to just exit and save the system the effort.
Edit: I have instances with hundreds (and hundreds) of dbs. We use a single QA instance for several prod instances.
Again, that's just my preference, not saying anyone else should do it, but that's how I code it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply