How to CREATE TYPE type_name AS existing_table_name

Is there a way to create table type in SQL Server 2008 based on scheme of existing table?

CREATE TABLE 开发者_StackOverflow中文版A (id INT, name VARCHAR(30))

CREATE TYPE type_a AS TABLE.A

Something like that.

No, this kind of composable DML is not yet possible. Microsoft has rejected this suggestion in the past, but with enough votes (e.g. more than 1!) it may get reconsidered in the future:

http://connect.microsoft.com/SQLServer/feedback/details/294130/table-valued-parameters-add-support-for-create-type-type-from-table-table-name-options-syntax-construct

You can use following stored procedure to create a type with same schema existing table may have.

Create PROCEDURE [dbo].[Sp_DefineTypeOutOfTableSchema]
@TableNames NVARCHAR(500)
AS
BEGIN

DECLARE @TableName NVARCHAR(100)
DECLARE @strSQL NVARCHAR(max)
DECLARE @strSQLCol NVARCHAR(1000)
DECLARE @ColName NVARCHAR(100)
DECLARE @ColDataTaype NVARCHAR(50)
DECLARE @ColDefault NVARCHAR(50)
DECLARE @ColIsNulable NVARCHAR(50)
DECLARE @ColCharMaxlen NVARCHAR(50)
DECLARE @ColNumPrec NVARCHAR(50)
DECLARE @ColNumScal NVARCHAR(50)


IF LEN(@TableNames) > 0 SET @TableNames = @TableNames + ',' 

WHILE LEN(@TableNames) > 0 
BEGIN

    SELECT @TableName = LTRIM(SUBSTRING(@TableNames, 1, CHARINDEX(',', @TableNames) - 1))

    DECLARE schemaCur CURSOR FOR 
    SELECT COLUMN_NAME,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@TableName

    OPEN schemaCur

    SELECT @strSQL=''

    FETCH NEXT FROM schemaCur
    INTO @ColName,@ColDataTaype,@ColIsNulable,@ColDefault,@ColCharMaxlen,@ColNumPrec,@ColNumScal

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @strSQLCol=''
        SELECT @strSQLCol= '['+@ColName+'] '+'[' + @ColDataTaype +'] '

        IF @ColDataTaype='nvarchar' or @ColDataTaype='char'  or @ColDataTaype='varchar' or @ColDataTaype='vchar'
            BEGIN
                SELECT @strSQLCol=@strSQLCol+ '(' + @ColCharMaxlen +') '
            END 
        ELSE IF @ColDataTaype='numeric' or @ColDataTaype='decimal' 
            BEGIN
                SELECT @strSQLCol=@strSQLCol +'(' + @ColNumPrec +',' +@ColNumScal + ') '
            END 

        IF @ColIsNulable='YES'
            BEGIN
                SELECT @strSQLCol=@strSQLCol+ 'NULL '
            END 
        ELSE
            BEGIN
                SELECT @strSQLCol=@strSQLCol+ ' NOT NULL '
            END 
        IF @ColDefault IS NOT NULL
            BEGIN
                SELECT @strSQLCol=@strSQLCol+ ' DEFAULT(' +@ColDefault + '),'
            END 
        ELSE
            BEGIN
                SELECT @strSQLCol=@strSQLCol+ ' ,'
            END

        SELECT @strSQL=@strSQL+@strSQLCol
            --print @strSQL
        FETCH NEXT FROM schemaCur
        INTO @ColName,@ColDataTaype,@ColIsNulable,@ColDefault,@ColCharMaxlen,@ColNumPrec,@ColNumScal

    END

    CLOSE schemaCur
    DEALLOCATE schemaCur

    --print @strSQL
    SELECT @strSQL=left( @strSQL, len(@strSQL)-1)
    --print @strSQL

    IF EXISTS (SELECT * FROM sys.types WHERE IS_TABLE_TYPE = 1 AND name = 't_' +@TableName)
    BEGIN           
        EXEC('DROP TYPE t_' +@TableName )
    END

    SELECT @strSQL =  'CREATE TYPE t_' + @TableName + ' AS TABLE (' +  @strSQL + ')'
    --print @strSQL
    EXEC (@strSQL)
    SELECT @TableNames = SUBSTRING(@TableNames, CHARINDEX(',', @TableNames) + 1, LEN(@TableNames))
END
END

you can use it like this

Exec Sp_DefineTypeOutOfTableSchema 'Table1name,Table2name'

You could experiment with creating a function that pulled the table definition out of sysobjects, systypes, syscolumns, syscomments, etc., and built a CREATE statement out of it. You'd just have to make sure to grab all of the important pieces (columns, constraints, comments, etc.) from the various sys tables.

Then call it like... EXEC myCREATEtable @template_table_name or some such...