Creating a SQL statement to copy the structure of a table

[ADoozer]

Hi folks.

I have recently taken over a project which has a lot of SQL related elements. (SQL is really not my field but I can muddle through it).

Anyway, the question.

When I go into SQL Server Management Studio and right click on my table and select "Create To", A nicely formatted SQL statement appears which allows me to create a copy of my table complete with constraints.

Basicaly, I need to perform this function (ie producing a nice "CREATE" SQL string) in code (using c sharp)

I have looked on Google and had a quick glance through the FAQ's here but can only find methods of copying a table without constraints.

Any help, pointers to code, pseudo code, etc etc would be greatly appreciated.

Dave Rigby
Systems Engineer




If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!

 

[ADoozer]

Just as an update this is what i have so far (and I hope there is a much easier/simpler way of doing it)

To get the column information I am using:

string mySQLGetColumnData = "SELECT syscolumns.name, systypes.name, syscolumns.[length], syscolumns.isnullable, syscolumns.colstat";
                mySQLGetColumnData += " FROM sysobjects";
                mySQLGetColumnData += " JOIN syscolumns ON sysobjects.id = syscolumns.id";
                mySQLGetColumnData += " JOIN systypes ON syscolumns.xtype=systypes.xtype";
                mySQLGetColumnData += " WHERE sysobjects.xtype='U' AND sysobjects.id = OBJECT_ID(N'[" + lbTables.Text +"]')";
                mySQLGetColumnData += " ORDER BY sysobjects.name,syscolumns.colid";

I cant figure out where the seed and incriment values are saved yet for the IDENTITY() bit and I am sure there is more information to pull from the columns...

For the primary key part I am using

string mySQLGetPrimaryKey = "SELECT A.CONSTRAINT_NAME,B.COLUMN_NAME";
                mySQLGetPrimaryKey += " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B";
                mySQLGetPrimaryKey += " WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME = '" + lbTables.Text + "'";

At a guess I will need to add in other keys and any indexes too

But so far when I piece it all together I end up with

CREATE TABLE NewTableName
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NOT NULL,
....
[8Gauge] [real] NOT NULL,
CONSTRAINT [PK_NewTableName] PRIMARY KEY CLUSTERED 
([ID] ASC) 
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]
) ON [PRIMARY]

Any input greatly appreciated

Dave Rigby
Systems Engineer

If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!

 

[simian336]

Just playing with it I came up with... This very basic and you would have to add lots more details.

declare @OldTableName varchar(100)
Declare @NewTableName varchar(100)
set @OldTableName ='reportperiod' -- your current table
set @NewTablename ='MyNewTable' -- what you want to name new table


select 'Create Table ' + @NewTablename + ' (' as mycolumn
union all

select ' [' + c.Name + '] ' + t.name +
Case
When t.name ='int' then ''
When t.name ='datetime' then ''
When t.name ='bit' then ''
When t.name ='nvarchar' then '(' +cast(c.max_length/2 as varchar(10)) + ')'
When t.name ='varchar' then '(' +cast(c.max_length as varchar(10)) + ')'
When t.name ='decimal' then '(' +cast(c.precision as varchar(10)) + ',' +
+cast(c.scale as varchar(10))+ ')'
End
+ ', ' as mycolumn
--, c.max_length, c.precision, c.scale, c.*
from sys.columns c
join sys.types t
on c.user_type_id = t.user_type_id
where object_id = (
select object_id
from sys.tables
where name=@OldTableName)

union all
select ')' as mycolumn


There are alot more details in the sys.columns table that would need to be added.

Simi

 

[hedgracer]

if you already have a table and are just copying the structure here is a great little piece of code given to me by poster gmmastros:

Select *
into DBO.Moneyline_One_201105
From DBO.Moneyline_One_201104
Where 1=0

The part 'Where 1=0' effectively filters out all the rows and you will only get the structure.

This works extremely well. Hope it helps.

 

[SimonSellick]

If you are using C#, perhaps you could use the SSMS object model and call whatever method it uses to generate a script? There's no guarantee that it exposes the method, nor, if it does, that you can capture its output; but it might be worth exploring - it could save a lot of work.

 

[gmmastros]

hedgracer,

That code will create a new table based on the column structure of another. What it won't do is create contraints, a primary key, foreign key, indexes, statistics, etc....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ADoozer]

thanks for the input guys.

I will have a look into the ssms object and see if that helps, but as I suspected it looks like i will need to pick apart the various system objects for the info.

I will be back looking at this code at some point this week so will post some more feedback then.

cheers all

If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!

 

[TheBugSlayer]

ADoozer,

Unless your DB is still in development or undergoes a lot of changes, a table structure does not change that often. Can you generate the script for the whole table, save it into a file and just execute the script file from your C# code? In a structured environment IT is notified of DB schema changes, so if the schema changes, you generate a new script and there you have it.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)