Potential Generate DDL 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

Potential Generate DDL Script

Author

Topic

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 16:03:00

Hello,

This procedure when accepting a table name as parameter will produce a Create Table statement, INsert INto statement, and produce the top 10 records from a table in a suitable format for posting.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[pGenerateDDL](@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' 
					 end
--Cast((SELECT TOP 1 value 
-- FROM ::fn_listextendedproperty(null, 'user', @user, @type, @tablename, 'column', default)
-- WHERE objname = C.name) as varchar)
		+ 
				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: '--TABLE STRUCTURE BELOW'
Print: 'Create Table ' + @TableName + '(' + @colstr

DECLARE @SQLstring varchar(8000)
DECLARE @SELString varchar(8000)
DECLARE @firstTime bit

SELECT @SQLstring = ''
SELECT @firstTime = 1
Select @SELstring = ''

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 @SELString = @SELString + 'Cast('+ '[' + @columnName + '] as varchar) +'',''+'
		SELECT @firstTime = 0
	END
	FETCH NEXT FROM getColumnsCursor INTO @columnName
END

CLOSE getColumnsCursor
DEALLOCATE getColumnsCursor

Print: '--BELOW IS INSERT STATEMENT.'

Select @SQLString = 
'Insert Into '+ @Tablename + ' ('+ @SQLString + ')'
 
Select @SELString = 'Select TOP 10 ''Select '' +'  + Left(@SELstring,len(@SELstring)-4) + ' +'' UNION ALL'' FROM ' + @Tablename 

Print (@SQLstring)
Print ''

Exec (@SELstring)

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

In my case, I tested on a small table and it produced this in the messages window:

--TABLE STRUCTURE BELOW
Create Table ALI_ExpectedLR(
[ALI_Score] int NULL,
[ALIExpectedLR] numeric(10,5) NULL,
[ALIExpectedLR_Unit] numeric(10,5) NULL)
--BELOW IS INSERT STATEMENT.
Insert Into ALI_ExpectedLR ([ALI_Score],
[ALIExpectedLR],
[ALIExpectedLR_Unit])

and this to the results pane:


Select 141,0.04086,0.07329 UNION ALL
Select 142,0.04013,0.07217 UNION ALL
Select 143,0.03940,0.07106 UNION ALL
Select 144,0.03868,0.06996 UNION ALL
Select 145,0.03797,0.06888 UNION ALL
Select 146,0.03727,0.06782 UNION ALL
Select 147,0.03658,0.06676 UNION ALL
Select 148,0.03589,0.06572 UNION ALL
Select 149,0.03522,0.06470 UNION ALL
Select 150,0.03455,0.06368 UNION ALL
[/code]



I am not sure if it is "handy" or not, but was an interesting thing to work on.  It is an extension of script added to madhivans post on Generating script (as an alternative).  Without writing another whole cursor, I didn't see an easy way to eliminate the last "UNION ALL"

If I am missing some fundamental logic or best practice that can be improved on, please make whatever changes are best.

If it is useful, let me know that as well.

Note: removed a bit that identified calculated columns for this purpose, as for the intended use, it isn't necessary.





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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-14 : 02:12:33

Good attempt dataguru
Couple of suggestions

1 If one of the column value is NULL then entire select statement becomes NULL.
2 you used cast(col as varchar). Note that if you dont specify the column length by default only 30 would be taken. So it is better to specify the exact lenght

Your original code with modifications highlighted




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter Procedure [dbo].[pGenerateDDL](@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(1000)) + ','+ cast(c.scale as varchar(1000)) + ')'
				 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' 
					 end
--Cast((SELECT TOP 1 value 
-- FROM ::fn_listextendedproperty(null, 'user', @user, @type, @tablename, 'column', default)
-- WHERE objname = C.name) as varchar)
		+ 
				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: '--TABLE STRUCTURE BELOW'
Print: 'Create Table ' + @TableName + '(' + @colstr

DECLARE @SQLstring varchar(8000)
DECLARE @SELString varchar(8000)
DECLARE @firstTime bit

SELECT @SQLstring = ''
SELECT @firstTime = 1
Select @SELstring = ''

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 @SELString = @SELString + 'Cast(coalesce('+ '[' + @columnName + '],'''') as varchar(1000)) +'',''+'
		SELECT @firstTime = 0
	END
	FETCH NEXT FROM getColumnsCursor INTO @columnName
END

CLOSE getColumnsCursor
DEALLOCATE getColumnsCursor

Print: '--BELOW IS INSERT STATEMENT.'

Select @SQLString = 
'Insert Into '+ @Tablename + ' ('+ @SQLString + ')'
 
Select @SELString = 'Select TOP 10 ''Select '' +'  + Left(@SELstring,len(@SELstring)-4) + ' +'' UNION ALL'' FROM ' + @Tablename 

Print (@SQLstring)
Print ''
Exec (@SELstring)

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Madhivanan

Failing to plan is Planning to fail

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 07:32:51

quote:
Originally posted by madhivanan

Good attempt dataguru
Couple of suggestions

1 If one of the column value is NULL then entire select statement becomes NULL.
2 you used cast(col as varchar). Note that if you dont specify the column length by default only 30 would be taken. So it is better to specify the exact lenght

Your original code with modifications highlighted

Madhivanan

Failing to plan is Planning to fail

Changes make perfect sense, wouldn't have caught those until I ran into an issue. I did test with a 145 column table and create statement worked, but I didn't use the same table to test the final Select statement.

Thanks!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources