MS-SQLで、メタ情報からCREATE TABLEを作成

MS-SQL2005以上で使用出来る

DECLARE @Table_Definition VARCHAR(MAX),
@TableName VARCHAR(25)

SET @TableName = 'Attachments'

SET @Table_Definition = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ' +
QUOTENAME(@TableName,'''') + ')' + CHAR(13) + 'BEGIN'

SET @Table_Definition = COALESCE(@Table_Definition + CHAR(13),'') + 'CREATE TABLE ('

SELECT @Table_Definition = COALESCE(@Table_Definition,'') +
CHAR(13) + '[' + COLUMN_NAME + ']' + SPACE(1) + '[' + DATA_TYPE + ']' +
CASE
WHEN UPPER(DATA_TYPE) IN ('REAL','MONEY','DECIMAL','NUMERIC') THEN
'(' + CONVERT(VARCHAR(3),COALESCE(numeric_precision,'')) + ','+
CONVERT(VARCHAR(3),COALESCE(Numeric_Scale,'')) + ')'
WHEN UPPER(DATA_TYPE) IN ('CHAR','NVARCHAR','VARCHAR','NCHAR') THEN
'(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'max' ELSE CAST(isnull(CHARACTER_MAXIMUM_LENGTH,'') AS VARCHAR) END + ')'
ELSE '' END +
CASE WHEN IS_NULLABLE = 'NO' THEN ' not null' ELSE ' null' END +
CASE WHEN COLUMN_DEFAULT IS NULL THEN ',' ELSE 'default ' + COLUMN_DEFAULT + ',' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName

SET @Table_Definition = STUFF(@Table_Definition,LEN(@Table_Definition),1,CHAR(13) + ')' + CHAR(13) + 'END')

PRINT @Table_Definition

--EXEC (@Table_Definition)