Potential Generate DDL Script

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])

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




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.