CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Cooxkie
Cooxkie

Reputation: 7380

How to create new table where database's name begin with ...?

I would like to know if there is a solution to create a new table in many databases depends on their name.

I'm using SQL Server 2008 R2.

Here is my script for create new table :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[E_Invent2](
    [D_No] [smallint] NOT NULL,
    [Mo_Droits] [nvarchar](1) NOT NULL,
    [Pr_Code] [nvarchar](10) NOT NULL,
    [Pr_Libellé] [nvarchar](50) NULL,
    [In_Stk08] [real] NULL,
    [In_Stk09] [real] NULL,
    [In_Stk10] [real] NULL,
    [In_Stk11] [real] NULL,
    [In_Stk12] [real] NULL,
    [In_Stk01] [real] NULL,
    [In_Stk02] [real] NULL,
    [In_Stk03] [real] NULL,
    [In_Stk04] [real] NULL,
    [In_Stk05] [real] NULL,
    [In_Stk06] [real] NULL,
    [In_Stk07] [real] NULL,
    [In_StkMoy] [real] NULL,
    [In_StkReel] [real] NULL,
    [In_PrtConstElab] [real] NULL,
    [In_TauxElab] [real] NULL,
    [In_DeducAutoElab] [real] NULL,
    [In_PrtTaxElab] [real] NULL,
    [In_PrtConstEmbout] [real] NULL,
    [In_VolEmbout] [real] NULL,
    [In_TauxEmbout] [real] NULL,
    [In_DeducAutoEmbout] [real] NULL,
    [In_PrtTaxEmbout] [real] NULL,
    [In_PrtTaxTotal] [real] NULL,
    [In_ManquantTaxable] [real] NULL,
    [In_TotalDroits] [real] NULL,
    [D_Nom] [nchar](50) NULL,
    [D_Enseigne] [nchar](30) NULL,
    [D_Adr1] [nchar](30) NULL,
    [D_Adr2] [nchar](30) NULL,
    [D_Post] [nchar](10) NULL,
    [D_Adr3] [nchar](30) NULL,
 CONSTRAINT [PK_E_Invent2] PRIMARY KEY CLUSTERED 
(
    [D_No] ASC,
    [Mo_Droits] ASC,
    [Pr_Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I would like to execute it for all databases where database's name begin with CM_0.

I found a solution to drop table :

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' drop table '+table_name from INFORMATION_SCEHMA.TABLES where table_name like 'tmp_%'
exec(@sql)

But 'drop' command line is simpler than 'create' command line...

Is anyone can help me ?

Thanks in advance

Cooxkie

Upvotes: 2

Views: 86

Answers (2)

anonxen
anonxen

Reputation: 824

Please check this:

Declare @Sql nvarchar(max) = ''
Declare @tablename nvarchar(max) = 'MyTab'
Declare @PreSyntax nvarchar(max) = 'SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE ' 

Declare @PostSyntax nvarchar(max) = '(
    [D_No] [smallint] NOT NULL,
    [Mo_Droits] [nvarchar](1) NOT NULL,
    [Pr_Code] [nvarchar](10) NOT NULL,
    [Pr_Libellé] [nvarchar](50) NULL,
    [In_Stk08] [real] NULL,
    [In_Stk09] [real] NULL,
    [In_Stk10] [real] NULL,
    [In_Stk11] [real] NULL,
    [In_Stk12] [real] NULL,
    [In_Stk01] [real] NULL,
    [In_Stk02] [real] NULL,
    [In_Stk03] [real] NULL,
    [In_Stk04] [real] NULL,
    [In_Stk05] [real] NULL,
    [In_Stk06] [real] NULL,
    [In_Stk07] [real] NULL,
    [In_StkMoy] [real] NULL,
    [In_StkReel] [real] NULL,
    [In_PrtConstElab] [real] NULL,
    [In_TauxElab] [real] NULL,
    [In_DeducAutoElab] [real] NULL,
    [In_PrtTaxElab] [real] NULL,
    [In_PrtConstEmbout] [real] NULL,
    [In_VolEmbout] [real] NULL,
    [In_TauxEmbout] [real] NULL,
    [In_DeducAutoEmbout] [real] NULL,
    [In_PrtTaxEmbout] [real] NULL,
    [In_PrtTaxTotal] [real] NULL,
    [In_ManquantTaxable] [real] NULL,
    [In_TotalDroits] [real] NULL,
    [D_Nom] [nchar](50) NULL,
    [D_Enseigne] [nchar](30) NULL,
    [D_Adr1] [nchar](30) NULL,
    [D_Adr2] [nchar](30) NULL,
    [D_Post] [nchar](10) NULL,
    [D_Adr3] [nchar](30) NULL,
 CONSTRAINT [PK_E_Invent2] PRIMARY KEY CLUSTERED 
(
    [D_No] ASC,
    [Mo_Droits] ASC,
    [Pr_Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]'

Select @Sql = @Sql + char(10) + @PreSyntax + isnull(name,'') + '.dbo.' + @tablename + @PostSyntax + char(10)
from sys.sysdatabases d
where d.name like '%pattern%'

Select @Sql

Upvotes: 0

Suvendu Shekhar Giri
Suvendu Shekhar Giri

Reputation: 1394

There is a better and cheaper way to do this. This is very very simple and works perfectly.

With SELECT INTO statement you can copy the structure of a table as well as data to another table in same or external databases.

Reference:http://www.w3schools.com/sql/sql_select_into.asp

DECLARE @sql VARCHAR(8000)
SET @sql=''
SELECT @sql=@sql+'; SELECT * INTO '+name+'.dbo.E_Invent2 FROM OriginalDB.dbo.E_Invent2' FROM sysdatabases WHERE name LIKE 'CM_0%' and name<>'OriginalDB'
SELECT @sql
EXEC(@sql)

Here OrigialDB is the name of database where you have this table.

If your table in OrginalDB carries data and you don't want to copy data and need to copy only the structure then you may try this-

DECLARE @sql VARCHAR(8000)   

SET @sql=''
    SELECT @sql=@sql+'; SELECT * INTO '+name+'.dbo.E_Invent2 FROM OriginalDB.dbo.E_Invent2 WHERE 1<>1' FROM sysdatabases WHERE name LIKE 'CM_0%' and name<>'OriginalDB'
    SELECT @sql
    EXEC(@sql)

This should work else let me know if I can help you.

NOTE: Constraints will not be copied

Upvotes: 2

Related Questions