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))
)
The reason for testing is to give the schools a tool to measure different students from different backgrounds on the same categories of learning on an equal footing.term paper The same rationale carries through from private school testing to admissions tests used when entering colleges and universities and then graduate schools.
ReplyDelete