Get Table Schema

-- -----------------------------------------------------------------------------
-- Get Table Schema
--
-- @database_name  varchar  The database name.
-- @table_name     varchar  The table name.
-- -----------------------------------------------------------------------------

declare @database_name varchar(20);
declare @table_name    varchar(20);

set @database_name = 'THE_DATABASE_NAME';
set @table_name    = 'THE_TABLE_NAME';

select COLUMN_NAME as name,
    DATA_TYPE as [type],
    CHARACTER_MAXIMUM_LENGTH as size,
    IS_NULLABLE as nullable,
    COLUMN_DEFAULT as [default],
    (
        select case
                when COUNT(1) > 0
                    then 'yes'
                else 'no'
                end
        from sys.index_columns as ic
        where (object_id = OBJECT_ID(@database_name))
            and (column_id = COLUMNPROPERTY(OBJECT_ID(@database_name), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'columnid'))
            and (key_ordinal = 1)
        ) as indexed,
    TABLE_SCHEMA as [schema],
    TABLE_CATALOG as [database],
    TABLE_NAME as [table]
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table_name
order by ORDINAL_POSITION;