How to have detail of tables from sql server using query? Table details

Question

After a few resaerches i notice that i needed a query to obtain all details of a table including pks and fks at SQL Server, anyone know any sp or any easy way?

I did the query below, but I think this information should be at some stored procedure or something alike.

select 
  sys.tables.name TableName,
  sys.tables.object_id TableId,
  sys.columns.name ColumnName,
  sys.types.name TypeName,
  coalesce(
    (
      select top 1
        sys.indexes.is_unique
      from 
        sys.index_columns 
        inner join sys.indexes on (sys.indexes.index_id = sys.index_columns.index_id)
      where 
        sys.columns.object_id = sys.index_columns.object_id 
        and sys.columns.column_id = sys.index_columns.column_id
        and sys.indexes.is_unique = 1
    ), 0
  ) IsUnique, 
  coalesce(
    (
      select top 1
        sys.indexes.is_primary_key
      from 
        sys.index_columns 
        inner join sys.indexes on (sys.indexes.index_id = sys.index_columns.index_id)
      where 
        sys.columns.object_id = sys.index_columns.object_id 
        and sys.columns.column_id = sys.index_columns.column_id
        and sys.indexes.is_primary_key = 1
    ), 0
  ) IsPrimaryKey,
  coalesce(
    (
      select top 1
        1
      from
        sys.foreign_key_columns
        inner join sys.foreign_keys on (sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id)
      where
        sys.foreign_key_columns.parent_object_id = sys.tables.object_id and parent_column_id=sys.columns.column_id
      ), 0
  ) IsForeignKey,
  sys.columns.max_length MaxLength,
  sys.columns.precision Precision,
  sys.columns.scale Scale,
  sys.columns.is_nullable IsNull,
  sys.columns.is_identity IsIdentity
from 
  sys.tables
  inner join sys.columns on (sys.tables.object_id = sys.columns.object_id)
  inner join sys.types on (sys.columns.system_type_id = sys.types.system_type_id)

Answer 1

Try making use of information_schema. You can frame your query as such:

Select * from information_schema.columns where table_name = 'City'

Consequently, you can also make use of sp_help as such:

exec sp_help 'table_name'

Use which ever suits your needs!!!

Answer 2

I believe you're talking about SP_HELP. E.g.

exec sp_help 'TABLE_NAME'