Copy table structure using sysobjects

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Copy table structure using sysobjects

Author

Topic

Frances
Starting Member

20 Posts

Posted - 2005-02-18 : 05:33:30

Hi all,

I'm trying to copy the tables from one database to another database. I only want to copy the structure, not the data. I know that I can do this in Enterprise Manager by right-clicking and choosing "Generate Script...", but that's exactly what I don't want

I have a feeling that this can be done with the sysobjects-table, but the "How" of it escapes me

Any ideas?

Thank you :)

Kristen
Test

22859 Posts

Posted - 2005-02-18 : 05:51:41

If you want a full blown script of the CREATE TABLE statements you'll need to use DMO.
http://www.nigelrivett.net/DMO/DMOScripting.html

If you just need something more Mickey Mouse you could use sysobjects and syscolumns


SELECT	CASE WHEN colid = 1 THEN 'CREATE TABLE ' + O.name + ' (' ELSE '' END
	+ C.name + ' '
	+ CASE C.xtype WHEN 167 THEN 'varchar(' + CONVERT(varchar(20), C.length) + ')'
			WHEN 61 THEN 'datetime'
--			...
			ELSE 'UnknownType_' + CONVERT(varchar(20), C.xtype)
			END + ' '
	+ CASE WHEN C.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END
	+ CASE WHEN colid = (SELECT MAX(colid) FROM dbo.syscolumns C2 WHERE C2.id = O.id)
		THEN ')'
		ELSE ','
		END
FROM	dbo.sysobjects O
	JOIN dbo.syscolumns C
		ON C.id = O.id
WHERE	    O.name= 'MyTable'
	AND O.type = 'U'
ORDER BY colid

Kristen

Frances
Starting Member

20 Posts

Posted - 2005-02-18 : 06:19:18

Thanks, I tried Nigels script, but it doesn't seem to work... No SQL-file is generated

The connection is made but all I get is :

output

NULL

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 08:18:07

here is donald duck version.

declare @tname sysname
set @tname = N'MyTable'
select
	case ordinal_position when 1 then 'create table ' + table_name + char(10) + '(' + char(10) + char(9) else char(9) + ',' end
	+ column_name
	+ ' '
	+ data_type
	+ case 	when data_type like('%char')
			then '(' + ltrim(character_maximum_length) + ')'
		when data_type in('numeric','decimal')
			then '(' + ltrim(numeric_precision) + ',' + ltrim(numeric_scale) + ')'
		else ''end
	+ case IS_NULLABLE when 'no' then ' NOT NULL' else ' NULL' end
from
	information_schema.columns
where
	table_name = @tname
union all select ')'

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-18 : 09:32:16

I have a Goofy version somewhere, where did I put that ....

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 10:00:51

Jeff -- you put it on Pluto.

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

factaky
Starting Member

2 Posts

Posted - 2005-02-24 : 22:08:05

Try this, got the idea from Transact SQL Programming.
Assume Db1 is source database and Db2 is destination database

use Db1
select * into Db2.dbo.Table1 from Table1 where 1=2

This will copy the structure only from Table1 of Db1 to Table1 of Db2.

factaky
Starting Member

2 Posts

Posted - 2005-02-24 : 22:21:26

Or try this, this will do what you want.

use Db1
select 'select * into Db2.dbo.' + name + ' from ' + name + ' where 1=2' from sysobjects where xtype = 'U'

The condition where 1=2 will ensure that no record will be copied.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-25 : 11:29:41

quote:
Originally posted by factaky

Or try this, this will do what you want.

use Db1
select 'select * into Db2.dbo.' + name + ' from ' + name + ' where 1=2' from sysobjects where xtype = 'U'

The condition where 1=2 will ensure that no record will be copied.

I did this:

use XERXES
select 'select * into MCACAPS.dbo.' + xtab_K + ' from ' + xtab_K + ' where 1=2' from sysobjects where xtype = 'U'

...but I got this error:

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'xtab_K'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'xtab_K'.

Why doesn't this work? Had you tried this out beforehand?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-25 : 11:32:37

there is no xtab_K column in sysobjects.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-25 : 11:33:14

quote:

Originally posted by rockmoose

here is donald duck version.

declare @tname sysname
set @tname = N'MyTable'
select
	case ordinal_position when 1 then 'create table ' + table_name + char(10) + '(' + char(10) + char(9) else char(9) + ',' end
	+ column_name
	+ ' '
	+ data_type
	+ case 	when data_type like('%char')
			then '(' + ltrim(character_maximum_length) + ')'
		when data_type in('numeric','decimal')
			then '(' + ltrim(numeric_precision) + ',' + ltrim(numeric_scale) + ')'
		else ''end
	+ case IS_NULLABLE when 'no' then ' NOT NULL' else ' NULL' end
from
	information_schema.columns
where
	table_name = @tname
union all select ')'

rockmoose

This one didn't work for me, either, Moose

. Why not?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-25 : 11:38:09

have you replaced MyTable in
set @tname = N'MyTable'
to your desired table name?
if you do
set @tname = N'Orders'
ad run it in northwind it works fine.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-25 : 14:27:41

quote:
Originally posted by spirit1

have you replaced MyTable in
set @tname = N'MyTable'
to your desired table name?
if you do
set @tname = N'Orders'
ad run it in northwind it works fine.

Go with the flow & have fun! Else fight the flow

Mladen,

How does this allow me to copy a table structure from one database to another?

Yeah, this worked in Northwind, but I don't find the second table shell anywhere

!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-25 : 15:10:35

The result of the script gives You a CREATE TABLE script,
You need copy/paste this script in QA than run it in the database where You want to create the table.
Hope this clarifies some points

rockmoose

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-25 : 15:13:34

quote:
Originally posted by rockmoose

The result of the script gives You a CREATE TABLE script,
You need copy/paste this script in QA than run it in the database where You want to create the table.
Hope this clarifies some points

rockmoose

Thanks, Moose

, I'd still be spinning my wheels if you hadn't have said something! Thanks!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-24 : 11:05:01

How do I use the code in a stored procedure so that I can get the 'CREATE TABLE..' script as a return/output of that procedure?

Thanks

quote:
Originally posted by rockmoose

The result of the script gives You a CREATE TABLE script,
You need copy/paste this script in QA than run it in the database where You want to create the table.
Hope this clarifies some points

rockmoose

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 12:26:59

quote:
Originally posted by umertahir

How do I use the code in a stored procedure so that I can get the 'CREATE TABLE..' script as a return/output of that procedure?

Thanks

quote:
Originally posted by rockmoose

The result of the script gives You a CREATE TABLE script,
You need copy/paste this script in QA than run it in the database where You want to create the table.
Hope this clarifies some points

rockmoose

Why are using Old thread (i.e 4 years old)? Create new thread.

Groker
Starting Member

2 Posts

Posted - 2011-04-07 : 18:27:57

Hi, I know this is an old thread, but since I used the above code to create new code, thought I would share it. Let's see, we got the Mickey Mouse and Donald Duck version... I'll call this the Bugs Bunny version.


--IF OBJECT_ID('dbo.fn_CopyTable') IS NOT NULL DROP FUNCTION dbo.fn_CopyTable
--GO

CREATE FUNCTION dbo.fn_CopyTable(
	@tname sysname
	,@Columns VARCHAR(MAX)
) RETURNS NVARCHAR(MAX) AS
BEGIN

DECLARE @RetVal NVARCHAR(MAX)

SELECT @RetVal = COALESCE(@RetVal+',','') +
	column_name
	+ ' '
	+ data_type
	+ case 	when data_type like('%char')
			then '(' + 
				CASE WHEN character_maximum_length = -1 THEN 'MAX'
				ELSE ltrim(character_maximum_length) END
			+ ')'
		when data_type in('numeric','decimal')
			then '(' + ltrim(numeric_precision) + ',' + ltrim(numeric_scale) + ')'
		else ''end
	+ ' NULL'
from
	information_schema.columns
where
	table_name = @tname
	AND COLUMN_NAME IN (SELECT item FROM .dbo.fnSplit(@Columns, ','))


SET @RetVal = 'create table ' + @tname + char(10) + '(' + char(10) + char(9)
	+ @RetVal
	+ ')'

RETURN @RetVal

END
--GO

--SELECT .dbo.fn_CopyTable('Document','Title,Body,Description')

gobsmacked
Starting Member

1 Post

Posted - 2011-05-12 : 09:41:11

Groker - much thanks for the code you submitted above. I'm building a generic script which creates an audit database (tables in audit db, update and delete triggers in orig db), and your code got me almost there. The only problem I'm having is adding a database name variable to the code, in the FROM clause (FROM + @dbname + '.information_schema.columns'). Without that extra bit the function must reside in the database I want to set up auditing on, which may mean in every database on the server. Any ideas?
thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources