sql server - Simplify SQL Column lookup Query -

i want query database sys tables list of table, column, , primary key information query:

select t.name [tablename] , (select max(column_id) sys.columns c c.object_id = t.object_id) [columncount] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 1) [column01] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 2) [column02] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 3) [column03] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 4) [column04] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 5) [column05] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 6) [column06] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 7) [column07] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 8) [column08] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 9) [column09] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 10) [column10] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 11) [column11] , (select name sys.columns c c.object_id = t.object_id , c.column_id = 12) [column12] , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 1) [column01primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 2) [column02primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 3) [column03primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 4) [column04primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 5) [column05primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 6) [column06primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 7) [column07primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 8) [column08primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 9) [column09primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 10) [column10primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 11) [column11primarykey]  , (select i.is_primary_key sys.columns c inner join sys.index_columns ic on ic.column_id = c.column_id , ic.object_id = c.object_id inner join sys.indexes on ic.index_id = i.index_id , ic.object_id = i.object_id , i.is_primary_key <> 0 t.object_id = c.object_id , c.column_id = 12) [column12primarykey]  sys.tables t  ; 

is there way simplify query instead of continuing add more columns manually?

try - give you're looking for?

select     columnname = c.name,     schemaname = s.name,      tablename = t.name,     i.name, i.is_primary_key     sys.columns c  inner join      sys.tables t on c.object_id = t.object_id inner join      sys.schemas s on t.schema_id = s.schema_id inner join      sys.indexes on i.object_id = t.object_id 

it should give column name, schema , table name, index name , whether or not index primary key