sql server msde: Is there any way to automatically create a "CREATE TABLE" statment using an
existing table?

I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).

Is there a way to do this?

cheers

nathan

hi Nathan,
[quoted text, click to view]

not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..

SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @COLUMN_NAME nvarchar(128),
@COLUMN_DEFAULT nvarchar(4000),
@IS_NULLABLE varchar(3),
@DATA_TYPE nvarchar(128),
@NUMERIC_PRECISION tinyint,
@NUMERIC_SCALE int,
@COLLATION_NAME nvarchar(128),
@CHARACTER_MAXIMUM_LENGTH smallint

DECLARE @cmd varchar(8000)
SET @cmd = ''

DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION

OPEN t
FETCH NEXT FROM t
INTO @COLUMN_NAME, @COLUMN_DEFAULT, @IS_NULLABLE, @DATA_TYPE,
@NUMERIC_PRECISION, @NUMERIC_SCALE, @COLLATION_NAME,
@CHARACTER_MAXIMUM_LENGTH

WHILE @@FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@cmd) <> 0 SET @cmd = @cmd + ' ,' + CHAR(10)
SET @cmd = @cmd + CHAR(9) + QUOTENAME( @COLUMN_NAME )+ ' ' + @DATA_TYPE + '
'

IF @DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @cmd = @cmd + '(' + CONVERT(varchar, @CHARACTER_MAXIMUM_LENGTH) + ') '

IF @DATA_TYPE = 'decimal' OR @DATA_TYPE = 'numeric'
SET @cmd = @cmd + '(' + CONVERT(varchar, @NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @NUMERIC_SCALE) + ') '

IF NOT @COLUMN_DEFAULT IS NULL
SET @cmd = @cmd + 'DEFAULT ' + @COLUMN_DEFAULT + ' '

IF NOT @COLLATION_NAME IS NULL
SET @cmd = @cmd + 'COLLATE ' + @COLLATION_NAME + ' '

IF @IS_NULLABLE = 'YES'
SET @cmd = @cmd + 'NULL '
ELSE
SET @cmd = @cmd + 'NOT NULL '

FETCH NEXT FROM t
INTO @COLUMN_NAME, @COLUMN_DEFAULT, @IS_NULLABLE, @DATA_TYPE,
@NUMERIC_PRECISION, @NUMERIC_SCALE, @COLLATION_NAME,
@CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t

SET @cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @cmd + CHAR(10)
+ CHAR(9) + ')'

SELECT @cmd

GO
DROP TABLE dbo.table1
--<----------------------------
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply