Write a sql query or script to get or generate the script or
query or text of create table statement in sql server.
Step 1: Create sql
function
ALTER FUNCTION GenerateTableScript(
@TableName VARCHAR(500)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @TableScript AS VARCHAR(MAX)
SET @TableScript = 'CREATE TABLE [' + @TableName + '] ('
SELECT @TableScript = @TableScript + '[' + c.name + '] ' +
CASE c.is_computed
WHEN 1 THEN ' AS ' + (SELECT [definition] FROM
sys.computed_columns WHERE
object_id = c.object_id)
ELSE ty.name +
CASE
WHEN ty.name = 'VARCHAR' OR ty.name = 'NVARCHAR' OR ty.name = 'CHAR' THEN '(' + CASE c.max_length WHEN -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')'
WHEN ty.name = 'DATETIME2' OR ty.name = 'TIME' THEN '(' + CAST(c.scale AS VARCHAR) + ')'
WHEN ty.name = 'NUMERIC' OR ty.name = 'DECIMAL' THEN '(' + CAST(c.precision AS VARCHAR)+ ',' + CAST(c.scale AS VARCHAR)+ ')'
ELSE ''
END +
CASE c.is_identity
WHEN 1 THEN (SELECT ' IDENTITY(' + CAST(ic.seed_value AS VARCHAR) + ',' + CAST(ic.increment_value AS VARCHAR) + ')' FROM sys.identity_columns ic
WHERE object_id
= c.object_id )
ELSE ''
END +
CASE c.is_nullable
WHEN 1 THEN ' NULL'
ELSE ' NOT NULL'
END
END +
CASE
WHEN c.column_id = t.max_column_id_used THEN
')'
ELSE ', '
END
FROM sys.tables t
INNER JOIN
sys.columns c ON t.object_id = c.object_id
INNER JOIN
sys.types ty ON ty.user_type_id= c.user_type_id
WHERE t.name
= @TableName
ORDER BY
c.column_id
RETURN @TableScript
END
Step 2: Execute the function
and pass the table name as parameter: For example:
SELECT dbo. GenerateTableScript('tblStu')
Sample output:
CREATE TABLE [tblStu] (
[one] bigint IDENTITY(5,3) NOT NULL,
[two] int NOT NULL,
[three] nvarchar(MAX) NULL,
[four] uniqueidentifier NULL,
[five] datetime2(7) NULL,
[six] uniqueidentifier NULL,
[seven] datetime NULL,
[eight] time(7) NULL,
[nine] decimal(18,3) NULL,
[ten] numeric(18,4) NULL,
[eleven] char(10) NULL,
[twelve] geography NULL,
[thirteen] AS ([two]+(10))
)
No comments:
Post a Comment