How to and where to use inbuilt objects in SQL server

There are multiple options to get database table and index details. Here I explain some useful objects and their use.

sp_help

Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-transact-sql?view=sql-server-ver15

sp_helpindex

Reports information about the indexes on a table or view. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpindex-transact-sql?view=sql-server-ver15

sp_helpconstraint

Returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint (for DEFAULT and CHECK constraints only). https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpconstraint-transact-sql?view=sql-server-ver15

sp_spaceused

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Table list with their last accessing date

select [schema_name]
 , table_name
 ,  max(last_access) as last_access 
  from(
      select schema_name(schema_id) as schema_name
         , name as table_name,
             (
             select max(last_access) 
              from (values(last_user_seek)
              , (last_user_scan)
              , (last_user_lookup)
              ,  (last_user_update)) as tmp(last_access)) as last_access
      from sys.dm_db_index_usage_stats sta
      join sys.objects obj    on obj.object_id = sta.object_id 
          and obj.type = 'U' and sta.database_id = DB_ID()
  ) usage
  group by schema_name,  table_name
  order by last_access desc;

Other Option

select [schema_name]
  , table_name
  , max(last_access) as last_access 
  from(
      select schema_name(schema_id) as schema_name, name as table_name,
          (
          select max(last_access) 
          from (values(last_user_seek)
             , (last_user_scan)
             , (last_user_lookup)
             ,  (last_user_update)) as tmp(last_access)
          ) as last_access
      from sys.dm_db_index_usage_stats sta
      join sys.objects obj on obj.object_id = sta.object_id 
      and obj.type = 'U' and sta.database_id = DB_ID()
      ) usage
  group by schema_name,  table_name
  order by last_access desc;

How to find Index information

How to find index information as per requirement. we can add fields as per our requirements.

SELECT s.name<br>
    , t.name<br>
    , i.name<br>
    , c.name <br>
FROM sys.tables t<br>
    INNER JOIN sys.schemas s on t.schema_id = s.schema_id<br>
    INNER JOIN sys.indexes i on i.object_id = t.object_id<br>
    INNER JOIN sys.index_columns ic on ic.object_id = t.object_id<br>
    INNER JOIN sys.columns c on c.object_id = t.object_id and ic.column_id = c.column_id<br>
WHERE i.index_id > 0     and i.type in (1, 2) -- clustered & nonclustered only<br>
      and i.is_primary_key = 0 -- do not include PK indexes<br>
      and i.is_unique_constraint = 0 -- do not include UQ<br>
      and i.is_disabled = 0 and i.is_hypothetical = 0 and ic.key_ordinal > 0<br>
ORDER BY ic.key_ordinal

Other option

SELECT      TableName = t.name<br>
    ,     IndexName = ind.name<br>
    ,     IndexId = ind.index_id<br>
    ,     ColumnId = ic.index_column_id<br>
    ,     ColumnName = col.name<br>
 FROM sys.indexes ind <br>
     INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id <br>
        and ind.index_id = ic.index_id <br>
     INNER JOIN sys.columns col ON ic.object_id = col.object_id <br>
        and ic.column_id = col.column_id <br>
     INNER JOIN sys.tables t ON ind.object_id = t.object_id <br>
 WHERE ind.is_primary_key = 0      AND ind.is_unique = 0      <br>
    AND ind.is_unique_constraint = 0 <br>
      AND t.is_ms_shipped = 0 <br>
 ORDER BY      t.name, ind.name, ind.index_id, ic.index_column_id;

How to find tables without Primary Key

How to find tables without primary key

select schema_name(tab.schema_id) as [schema_name], <br>
     tab.[name] as table_name<br>
 from sys.tables tab<br>
     left outer join sys.indexes pk   on tab.object_id = pk.object_id   <br>
        and pk.is_primary_key = 1<br>
 where pk.object_id is null<br>
 order by schema_name(tab.schema_id),    tab.[name]

Other option

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName<br>
 FROM sys.tables<br>
 WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0<br>
 ORDER BY SchemaName, TableName;