Create a sql stement from existing tables

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,
Nathan wrote:
> 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?
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/de...a-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.shtmhttp://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

counter of the group by statment

hi,

Lets say I have a table called test

create table test(

number int,

[str] nvarchar(50)

)

table content, for example:

8,'a'

8,'b'

8,'c'

5,'a'

5,'h'

I want to get a result with one more column that indicates on the asc counter

of the group by clause. i.e.

somthing like this:

number counter

8, 1

5, 2

and so on...

it does not realy matter regarding the query - All I want is to add one more column to the query that indicates on the count of the group by.so if i have 2 rows with number 8 and 2 rows with number 5 I would like to see in that column 1 (at the 8 number rows) and 2 (on the 5 number rows) and so on.. as the number is changing the counter is going up by 1.

Hi,

I think you might need to take a look at the RANK function: http://msdn2.microsoft.com/en-us/library/ms176102.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

You can use the dense_rank function to do this. The sample query assumes that you want to return all rows

create table #test(

number int,

[str] nvarchar(50)

)

insert into #test
select 8,'a'
union all
select 8,'b'
union all
select 8,'c'
union all
select 5,'a'
union all
select 5,'h'

select number,
dense_rank() over (order by number) as dense_rank
from #test

drop table #test
go

number dense_rank
-- --
5 1
5 1
8 2
8 2
8 2

|||

Hi,

Thank you for this info, but I need to use this rank in my where clause:

select number,

dense_rank() over (order by number) as dense_rank

from #test

where dense_rank = 1

but i get an error. How can I use it in the where clause?

thx