TableInformation

DECLARE @data VARCHAR(100) = 'RoleLookUp'
DECLARE @stt VARCHAR(100) = '83'
DECLARE @SCHEMA VARCHAR(100) = 'tmp'
select '2.' + @stt + ' ' + @SCHEMA + '.' + @data + ' Table','','','','','','' union all
select 'Column Name','Data Type','Null','Index','Key','Default','Description' union all
SELECT --cl.TABLE_NAME,
c.NAME                          'Column Name',
CASE
  WHEN t.NAME = 'int' THEN t.NAME
  WHEN t.NAME = 'varchar' THEN t.NAME + '('
                               + case when Cast(c.max_length AS VARCHAR(100)) = '-1' then 'max' else Cast(c.max_length AS VARCHAR(100)) end + ')'
  WHEN t.NAME in ('datetime','bit','int','float','bigint','tinyint','date','timestamp') THEN t.NAME
  WHEN t.NAME = 'nvarchar' THEN t.NAME + '('
                               + case when Cast(c.max_length AS VARCHAR(100)) = '-1' then 'max' else Cast(c.max_length AS VARCHAR(100)) end + ')'
  WHEN t.NAME = 'varbinary' THEN t.NAME + '('
                               + case when Cast(c.max_length AS VARCHAR(100)) = '-1' then 'max' else Cast(c.max_length AS VARCHAR(100)) end + ')'
  ELSE '--------------' + t.NAME  + '('
                               + Cast(c.max_length AS VARCHAR(100)) + ')'
END                             'Data Type',
CASE
  WHEN c.is_nullable = 1 THEN 'V'
  ELSE 'X'
END                             'Null',
CASE
  WHEN Isnull(i.index_id, 0) = 0 THEN 'X'
  ELSE 'V'
END                             'Index',
CASE
  WHEN Isnull(i.is_primary_key, 0) = 1 THEN 'PK'
  ELSE ''
END                             'Key',
CASE
  WHEN c.default_object_id = 0 THEN 'X'
  ELSE 'V'
END                             'Default',
--'Column' + Cast(c.column_id AS VARCHAR(100)) +
c.NAME+
 CASE WHEN c.is_identity = 1 THEN
' with Auto number' ELSE '' END 'Description'
--, CASE
--  WHEN c.default_object_id = 0 THEN ''
--  ELSE ''
--       + Substring(cl.column_default, 2, Len(cl.column_default)-2)
--END                             'Default'
FROM   sys.columns c
       INNER JOIN sys.types t
               ON c.user_type_id = t.user_type_id
       INNER JOIN (SELECT b.id,
                          table_schema,
                          table_name,
                          column_name,
                          column_default
                   FROM   information_schema.columns a
                          INNER JOIN sys.sysobjects b
                                  ON table_name = b.NAME
                   WHERE  table_schema = @SCHEMA) cl
               ON c.object_id = cl.id
                  AND c.NAME = cl.column_name
       LEFT OUTER JOIN sys.index_columns ic
                    ON ic.object_id = c.object_id
                       AND ic.column_id = c.column_id
       LEFT OUTER JOIN sys.indexes i
                    ON ic.object_id = i.object_id
                       AND ic.index_id = i.index_id
WHERE  c.object_id = Object_id(@SCHEMA + '.' + @data)