SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Generate SQL Script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/29/2005 :  09:36:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
--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

Slovenia
11751 Posts

Posted - 07/29/2005 :  12:17:05  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/01/2005 :  04:40:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2005 :  09:25:52  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/02/2005 :  09:32:52  Show Profile  Visit spirit1's Homepage  Reply with Quote
i think vyas or nigel have already this sproc...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2005 :  09:48:26  Show Profile  Reply with Quote
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
Go to Top of Page

jdelisle
Starting Member

1 Posts

Posted - 10/18/2007 :  10:10:42  Show Profile  Reply with Quote
madhivanan,

I like your script, but it doesn't account for Identity columns.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/19/2007 :  02:15:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/10/2007 :  13:49:45  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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


Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/10/2007 :  14:26:10  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

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]
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/13/2007 :  02:32:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Is pScriptME just alternate to my GenerateScript procedure?
or does it include any extra features?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/13/2007 :  13:29:39  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

luckysiri
Starting Member

USA
1 Posts

Posted - 01/06/2011 :  15:57:20  Show Profile  Reply with Quote
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

Edited by - luckysiri on 01/06/2011 16:00:54
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/12/2011 :  05:45:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
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

Edited by - madhivanan on 01/12/2011 05:46:54
Go to Top of Page

haaningnator
Starting Member

Denmark
1 Posts

Posted - 10/09/2011 :  11:17:58  Show Profile  Reply with Quote
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: http://msdn.microsoft.com/en-us/library/ms175007.aspx
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000