Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating a SQL statement to copy the structure of a table

Status
Not open for further replies.

ADoozer

Programmer
Dec 15, 2002
3,487
AU
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!
 
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:

Code:
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

Code:
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

Code:
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!
 
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
 
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.
 
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.
 
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
 
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 [peace]

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

Jack of all trades, king of none!
 
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top