Generate Table's Script by Query SQL

--CREATED BY: frgunawan
CREATE PROC [dbo].table_helptext
@TableName varchar(50)
AS
SET NOCOUNT ON
DECLARE @result  varchar(8000)
IF EXISTS(SELECT * FROM sysobjects WHERE name=@TableName)
BEGIN
SET @result = 'CREATE TABLE [dbo].[' + @TableName + ']' + char(10) + '(' + char(10)
SELECT @result = @result + sc.Name + ' ' +
st.Name + CASE WHEN st.Name in ('varchar','varchar','char','nchar') then '(' +
CASE WHEN cast(sc.Length as varchar)=-1 THEN 'MAX' ELSE cast(sc.Length as varchar) END + ') '
ELSE ' ' END +
CASE WHEN sc.IsNullable = 1 then 'NULL' ELSE 'NOT NULL' END +
CASE WHEN scm.text is not null then ' DEFAULT '+ scm.text ELSE ' ' END +
',' + char(10)
FROM sysobjects so
LEFT JOIN syscolumns sc on sc.id = so.id
LEFT JOIN systypes st on st.xusertype = sc.xusertype
LEFT JOIN syscomments scm on sc.cdefault = scm.id
WHERE so.name = @TableName
ORDER BY
sc.ColID
--cek ada Primary Key atau tidak
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE CONSTRAINT_NAME = (SELECT name FROM sysobjects WHERE parent_obj=object_id(@TableName) and xtype='PK'))
BEGIN
SET @result = @result + 'Primary Key('
SELECT @result = @result + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE CONSTRAINT_NAME = (SELECT name FROM sysobjects WHERE parent_obj=object_id(@TableName) and xtype='PK')
SET @result = SUBSTRING(@result,1,len(@result) - 1)+ ')'
END
ELSE
BEGIN
SET @result = SUBSTRING(@result,1,len(@result) - 2)
END
SELECT @result + char(10) + ')'
END
ELSE
BEGIN
SET @result = 'Invalid object name ' + @TableName + '.'
SELECT @result
END