Saturday, March 28, 2020

How to and where to use inbuilt objects in SQL server

 

Exploring SQL Server Tools for Table, Index, and Constraint Information

Managing SQL Server databases often involves examining table structures, indexes, and usage patterns. SQL Server provides several built-in system stored procedures and views to help DBAs and developers gather this information efficiently. In this article, we’ll walk through useful tools and sample queries for inspecting database objects and their properties.

1. Retrieve Metadata with Built-in Stored Procedures

🔍 sp_help

The sp_help stored procedure returns details about a specific database object such as tables, views, or user-defined data types. It pulls metadata from the sys.sysobjects view.

📘 Documentation:
sp_help (Transact-SQL)

🔍 sp_helpindex

Use this procedure to get information about indexes defined on a table or view, including index name, columns involved, and index type.

📘 Documentation:
sp_helpindex (Transact-SQL)

🔍 sp_helpconstraint

This procedure lists all constraints (PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, etc.) on a table, including the constraint name and related columns.

📘 Documentation:
sp_helpconstraint (Transact-SQL)

🔍 sp_spaceused

sp_spaceused provides size-related statistics for a table or the entire database, including reserved and used disk space and row counts.

2. Identify Recently Accessed Tables

You can track when a table was last accessed by users through different query operations (seek, scan, lookup, update). This can help identify stale or unused tables.

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 AS sta
    JOIN sys.objects AS obj 
        ON obj.object_id = sta.object_id 
        AND obj.type = 'U' 
        AND sta.database_id = DB_ID()
) AS usage
GROUP BY schema_name, table_name
ORDER BY last_access DESC;

This query can be customized or reused with minor changes depending on your needs.

3. Fetch Index Details

There are multiple ways to retrieve detailed index information:

✅ Index Columns with Table and Schema

SELECT s.name AS SchemaName,
       t.name AS TableName,
       i.name AS IndexName,
       c.name AS ColumnName
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.indexes i ON i.object_id = t.object_id
JOIN sys.index_columns ic ON ic.object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id AND ic.column_id = c.column_id
WHERE i.index_id > 0
  AND i.type IN (1, 2) -- Clustered & Non-clustered
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND i.is_disabled = 0
  AND i.is_hypothetical = 0
  AND ic.key_ordinal > 0
ORDER BY ic.key_ordinal;

✅ Alternate Index Metadata View

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

4. Find Tables without a Primary Key

Identifying tables without a primary key is critical for enforcing data integrity and improving performance.

✅ Option 1: Using sys.indexes

SELECT schema_name(tab.schema_id) AS schema_name,
       tab.name AS table_name
FROM sys.tables tab
LEFT JOIN sys.indexes pk 
    ON tab.object_id = pk.object_id AND pk.is_primary_key = 1
WHERE pk.object_id IS NULL
ORDER BY schema_name(tab.schema_id), tab.name;

✅ Option 2: Using OBJECTPROPERTY

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

Final Thoughts

Understanding and analyzing database objects is essential for efficient database administration. SQL Server offers powerful system views and stored procedures that reveal detailed metadata about tables, indexes, constraints, and usage. The examples above are great starting points for building your own diagnostic queries.

Have you used these tools in your environment? Share your experience or any other helpful queries you use!

at

Popular Posts