Generate SQL Script - SQL Server Forums

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

General SQL Server Forums

Script Library

Generate SQL Script

Author

Topic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 09:36:16

--This procedure will generate the Structure of a table

Create Procedure GenerateScript (
@tableName varchar(100))
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name=@tableName)
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')

set @sql=''
Select @sql=@sql
+case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end
+case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+
','+case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar)
end when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end
+case when Data_Type in (Select datatype from @cols)then '' else ')' end
+case when Is_Nullable='No' then ' Null,' else ' Not null,' end
from Information_Schema.COLUMNS where Table_Name=@tableName
select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-29 : 12:17:05

nice script

but what's wrong with QA's "script object to new window as ... Create, alter, drop...."?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-01 : 04:40:22

>>but what's wrong with QA's "script object to new window as ... Create, alter, drop...."?

Nothing wrong.
Just a try from me

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:25:52

Mad: Can you extend this to output the first N rows of data as INSERT statements, and then we can just get Noobies to run it and post the DDL so we can solve their incomprehensible questions!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-02 : 09:32:52

i think vyas or nigel have already this sproc...

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

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:48:26

To be clear:

Generate DDL for table, PK, constraints, indexes, FKs

Plus generate INSERTS for first 50 (or so) rows

I think a script, rather than an SProc, would be better for the job - saves having to create it into a, possibly, production database.

Am I being daft, or could we really say "Change the table name at the top, then just Run this, and cut&paste the results here"??

Kristen

jdelisle
Starting Member

1 Post

Posted - 2007-10-18 : 10:10:42

madhivanan,

I like your script, but it doesn't account for Identity columns.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-19 : 02:15:26

quote:
Originally posted by jdelisle

madhivanan,

I like your script, but it doesn't account for Identity columns.

It does but it doesnt show it in the create table script
I need to modify the procedure to show identity, primary key, etc

Madhivanan

Failing to plan is Planning to fail

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-10 : 13:49:45

I thought this was interesting, so I gave it a try. I can produce the create table properly, but I haven't figured out how to work in the computed column definition if one exists. Still working on the piece to find that computed formula propery and the primary key/index part. Mods can move or delete this if it is bunk, but I thought it an interesting thing to try,even though it isn't something needed.

the result is a functioning create table statement (with no prim key, index or extended properties (yet) ).

[CODE]

Create Procedure [dbo].[pScriptME](@Tablename varchar(128))
AS
Declare @Structure varchar(8000),
@colstr varchar(8000)

Set NOCOUNT on

Select @colstr = ''
If exists (select * from sysobjects where name = 'cols')
Drop Table cols

Create Table cols (
ColInfo varchar(500) null
)
Insert Into Cols (ColInfo)
Select '[' + RTRIM(C.name) + '] '
+ Case When isComputed = 0 then
LEFT(CASE
WHEN (T.name IN ('char', 'varchar', 'nchar','nvarchar')) THEN T.name + '(' + LTRIM(RTRIM(STR(C.length))) + ')'
When t.name in ('numeric','decimal') then t.name + '(' + Cast(c.prec as varchar) + ','+ cast(c.scale as varchar) + ')'
else t.name
END,30) else 'AS ' end
+

Case when isnullable = 1 and iscomputed = 0 then ' NULL'
When isnullable = 0 and iscomputed = 0 then ' NOT NULL'
When iscomputed =1 then '(calculated) ' end
+
Case When c.colid = (Select max(c.colid) maxid
FROM sysobjects o left JOIN syscolumns c ON (o.id = c.id)
left JOIN systypes t ON (c.xusertype = t.xusertype)
WHERE o.name = @tablename
) then ')' else ',' end
FROM sysobjects o inner JOIN syscolumns c ON (o.id = c.id)
inner JOIN systypes t ON (c.xusertype = t.xusertype)
WHERE o.name = @tablename

---
Declare colcur Cursor
READ_ONLY
FOR
Select Cast(Colinfo as varchar(500))
FROM cols

OPEN ColCur
FETCH colcur into @structure
IF (@@FETCH_STATUS <> 0)
BEGIN -- No matching objects
CLOSE TableCursor
DEALLOCATE TableCursor
END

WHILE (@@FETCH_STATUS = 0)
BEGIN
Select @colstr = @colstr + '
' + cast(@structure as varchar(500))
FETCH colcur INTO @structure
END
CLOSE colcur
DEALLOCATE colcur
If exists (select * from sysobjects where name = 'cols')
Drop Table cols

Print: 'Create Table ' + @TableName + '('
Print: @colstr

[/code]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-10 : 14:26:10

This one will generate insert Into script for a table name passed as variable. Still working on getting the first 10-20 rows with 'SELECT' and 'UNION ALL' but it is tripping me up. Oh well. Would be cool if there was a procedure people could just run and be able to post the create/insert into and first 20 rows of data so that responding is easier.
[code]
CREATE Proc [dbo].[pScriptInsertTop50] (@tablename varchar(128))
as
DECLARE @SQLstring varchar(8000)
DECLARE @firstTime bit

SELECT @SQLstring = ''
SELECT @firstTime = 1

DECLARE getColumnsCursor CURSOR
READ_ONLY
FOR
SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo' AND
c.TABLE_NAME = @Tablename

DECLARE @columnName nvarchar(128)
OPEN getColumnsCursor

FETCH NEXT FROM getColumnsCursor INTO @columnName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
IF (@firstTime = 0)
SELECT @SQLstring = @SQLstring + ','

-- append our column to the UPDATE statement
SELECT @SQLstring = @SQLstring + '[' + @columnName + ']'

SELECT @firstTime = 0
END
FETCH NEXT FROM getColumnsCursor INTO @columnName
END

CLOSE getColumnsCursor
DEALLOCATE getColumnsCursor

Select @SQLString =
'Insert Into '+ @Tablename + ' ('+ @SQLString + ')'

Print (@SQLstring)

[/CODE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 02:32:13

Is pScriptME just alternate to my GenerateScript procedure?
or does it include any extra features?

Madhivanan

Failing to plan is Planning to fail

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 13:29:39

I suppose it is an alternate. If my post doesn't belong, it can be removed...

It generates a pasteable and useable Create table statement in the messages/results window (but will fail for computed columns).

I have to add in a bit to capture the Index/Primary key as part of the create statement, and I still have to work in the computed column definition (as of right now it merely shows the datatype as (computed) but no formula.)

I was going to modify the Insert into script to produce a paste-able set of data, but haven't had time. Once the whole thing is done, the idea would be to generate a pasteable create/insert sequence with a table name passed as a variable such that people could just pass their table through the proc and paste the results here.

Poor planning on your part does not constitute an emergency on my part.

luckysiri
Starting Member

1 Post

Posted - 2011-01-06 : 15:57:20

I see it's a 4 year old thread, but I like to thank the posted user.

It is indeed a Cool Script.. Helped me when I was breaking my head for solution..

I did some modifications to script (heart & soul is belongs to posted script, mine is just a simplified version, I hope you don't mind

)..

CREATE PROCEDURE [dbo].[GenerateTableDefinition] (
@TableName VARCHAR(100))
AS
BEGIN

IF EXISTS(SELECT [Table_Name] FROM Information_Schema.COLUMNS WHERE [TABLE_NAME] =@tableName)

BEGIN
DECLARE @Sql VARCHAR(8000)
DECLARE @Table VARCHAR(100)

SET @Sql=''

SELECT @Sql=@Sql +
CASE
WHEN CHARINDEX('(',@Sql,1)<=0
THEN '(' ELSE '' END + [COLUMN_NAME] + ' ' + [DATA_TYPE] +
CASE
WHEN [DATA_TYPE] in ('real','money','decimal','numeric')
THEN + '(' + CAST(ISNULL([NUMERIC_PRECISION],'') AS VARCHAR)+ ',' +
CASE
WHEN [DATA_TYPE] in ('real','money','decimal','numeric')
THEN CAST(ISNULL([NUMERIC_SCALE],'') AS VARCHAR) + ')'
END
WHEN [DATA_TYPE] in ('char','nvarchar','varchar','nchar')
THEN + '(' + CAST(ISNULL([CHARACTER_MAXIMUM_LENGTH],'') AS VARCHAR) + ' )' ELSE ''
END +
CASE
WHEN [IS_NULLABLE] = 'No'
THEN ' Not Null,'
ELSE ' Null, '
END
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @TableName

SELECT @Table= 'Create table ' + [TABLE_NAME]
FROM [Information_Schema].[COLUMNS]
WHERE [TABLE_NAME]= @TableName

SELECT @Sql= @Table + SUBSTRING(@Sql,1,LEN(@sql)-1) +' )'

SELECT @Sql AS DDL
END
ELSE

SELECT 'The table '+@TableName + ' does not exist'

END

Thanks,
Lakshmi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-12 : 05:45:47

<<
It is indeed a Cool Script.. Helped me when I was breaking my head for solution..
>>

Thanks for the feedback

<<
I did some modifications to script (heart & soul is belongs to posted script, mine is just a simplified version, I hope you don't mind )..
>>

No problem.

Madhivanan

Failing to plan is Planning to fail

haaningnator
Starting Member

1 Post

Posted - 2011-10-09 : 11:17:58

First of all thanks for the the great piece of code.

I need to do "select * into table2 from table1 where 1=0" on a platform which dosent support this syntax and this thread was very helpful, for doing this through a storede procedure instead.

There does seem to be a small bug in some of the versions here.
As far as i have tested the datatypes real & money cant be declarede with precision/scale.

I have two questions i hope someone can help with
1) Have anyone tested this with user definede data types. From what i read they can in theory be tables: [url]http://msdn.microsoft.com/en-us/library/ms175007.aspx[/url]
2) will the columns returned from INFORMATION_SCHEMA.COLUMNS always be in the same order as they appear in the table or should i sort on ORDINAL_POSITION if i want to guarantee this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources