Foreign Key scripts

It's helpful to maintain ready-to-use scripts for resetting database contents, including reseeding identities. Feel free to adjust these scripts to fit your specific needs.

Drop Temporary Table

IF OBJECT_ID('tempdb..#Fkey') IS NOT NULL
    DROP TABLE #Fkey;
  

Create Temporary Table with Foreign Key Information

;WITH cte AS (
    SELECT 
        fkc.constraint_column_id AS consColumn,
        fk.NAME AS foreignKeyName,
        parentSchema.name AS parentSchema,
        parentTable.NAME AS parentTableName,
        parent_col.NAME AS parentColName,
        refSchema.name AS refSchema,
        refTable.NAME AS refTableName,
        ref_col.NAME AS refColName
    FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.tables parentTable ON parentTable.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas parentSchema ON parentSchema.schema_id = parentTable.schema_id
    INNER JOIN sys.columns parent_col ON fkc.parent_column_id = parent_col.column_id AND parent_col.object_id = parentTable.object_id
    INNER JOIN sys.tables refTable ON refTable.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas refSchema ON refSchema.schema_id = refTable.schema_id
    INNER JOIN sys.columns ref_col ON fkc.referenced_column_id = ref_col.column_id AND ref_col.object_id = refTable.object_id
    WHERE parentTable.type = 'U' AND refTable.type = 'U'
)
SELECT DISTINCT 
    foreignKeyName,
    parentSchema,
    parentTableName,
    SUBSTRING((
        SELECT ',' + a.parentColName
        FROM cte a
        WHERE a.foreignKeyName = c.foreignKeyName
        ORDER BY a.consColumn
        FOR XML PATH('')
    ), 2, 300000) AS parentColName,
    refSchema,
    refTableName,
    SUBSTRING((
        SELECT ',' + b.refColName
        FROM cte b
        WHERE b.foreignKeyName = c.foreignKeyName
        ORDER BY b.consColumn
        FOR XML PATH('')
    ), 2, 300000) AS refColName
INTO #Fkey
FROM cte c;
  

Generate Scripts to Drop Foreign Keys

-- Generate scripts to drop existing foreign key constraints
SELECT DISTINCT 
    'IF EXISTS (SELECT * FROM sys.foreign_keys 
        WHERE object_id = OBJECT_ID(N''[' + parentSchema + '].[' + foreignKeyName + ']'') 
        AND parent_object_id = OBJECT_ID(N''[' + parentSchema + '].[' + ParentTableName + ']''))
    ALTER TABLE [' + parentSchema + '].[' + ParentTableName + '] DROP CONSTRAINT [' + foreignKeyName + ']' 
AS foreignKey_drop_script
FROM #Fkey;
  

Generate Scripts to Recreate Foreign Keys

-- Generate scripts to recreate the foreign key constraints
SELECT DISTINCT 
    'ALTER TABLE [' + parentSchema + '].[' + ParentTableName + '] WITH CHECK 
    ADD CONSTRAINT [' + foreignKeyName + '] FOREIGN KEY(' + parentColName + ') 
    REFERENCES [' + refSchema + '].[' + refTableName + '](' + refColName + ')' 
AS Add_constraints_script
FROM #Fkey;
GO
  

Additional Resources:

• How to Find SQL Server Object Dependency
• Identifying Missing Indexes in SQL Server
• Missing Foreign Key Indexes

Suspect Pages table in SQL server

This table tracks pages that encountered a minor 823 error or an 824 error, listing one row per problematic page. While these pages are flagged as potentially corrupt, they might still be intact. The **event_type** column is updated once a suspect page is repaired, reflecting its new status.

SELECT 
    DB_NAME(database_id) AS [Database Name],
    [file_id],
    page_id,
    event_type,
    error_count,
    last_update_date
FROM 
    msdb.dbo.suspect_pages WITH (NOLOCK)
ORDER BY 
    database_id
OPTION (RECOMPILE);

For more details, refer to the official Microsoft documentation on suspect_pages.

How to Find Compressed Objects and Their Properties in SQL Server

📦 How to Find Compressed Objects and Their Properties in SQL Server

SQL Server offers data compression to help reduce storage requirements and improve I/O performance. Over time, it's important to review which objects in your database are compressed, what type of compression is used, and whether further optimization or decompression might be needed.

The following query helps you identify compressed objects in your database along with key details, such as the compression type and a ready-to-run command to remove compression if needed.

🧾 SQL Query to List Compressed Database Objects

SELECT 
    SCHEMA_NAME(o.schema_id) AS [Schema Name],
    OBJECT_NAME(o.object_id) AS [Object Name],
    'ALTER INDEX ALL ON ' + SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) + 
    ' REBUILD WITH (DATA_COMPRESSION = None);' AS [Decompress CMD],
    (
        SELECT OBJECTPROPERTY(OBJECT_ID(OBJECT_NAME(o.object_id)), 'TableHasVarDecimalStorageFormat')
    ) AS [Table Has VarDecimal Storage Format],
    p.[rows],
    p.[data_compression_desc] AS [Compression Type],
    p.[index_id] AS [Index ID on Table]
FROM 
    sys.partitions p
INNER JOIN 
    sys.objects o ON p.object_id = o.object_id
WHERE 
    p.data_compression > 0
    AND SCHEMA_NAME(o.schema_id) <> 'sys'
ORDER BY 
    [Schema Name],
    [Object Name];

🧠 What This Query Does

• Retrieves a list of all compressed objects (tables or indexes) in the current database.
• Shows the schema and object names for clarity.
• Displays the compression type (ROW, PAGE, or COLUMNSTORE).
• Provides an ALTER INDEX command to decompress each object if needed.
• Checks whether a table uses the VarDecimal Storage Format, which helps save space for decimal values.

✅ Why This Is Useful

• Helps DBAs audit storage savings across the database.
• Assists in evaluating whether certain tables or indexes should be recompressed, left as-is, or decompressed.
• Supports performance tuning efforts when analyzing I/O or CPU trade-offs for compression.

---

💡 Tip: Regularly reviewing compressed objects is a good habit—especially in environments where performance and storage costs are closely monitored.

How to find the missing foreign key indexes

Finding Missing Indexes on Foreign Key Relation Tables

In this article, I'm exploring how to identify missing indexes in tables with foreign key relationships.

SELECT DB_NAME() AS DBName
    , rc.Constraint_Name AS FK_Constraint
    , ccu.Table_Name AS FK_Table
    , ccu.Column_Name AS FK_Column
    , ccu2.Table_Name AS ParentTable
    , ccu2.Column_Name AS ParentColumn
    , I.Name AS IndexName
    , CASE WHEN I.Name IS NULL
        THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes
                    WHERE object_id = OBJECT_ID(N''' + RC.Constraint_Schema + '.' + ccu.Table_Name + ''') 
            AND name = N''IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ''') ' + '
            CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ' 
            ON ' + rc.Constraint_Schema + '.' + ccu.Table_Name + '( ' + ccu.Column_Name + ' ASC ) 
            WITH (PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = ON
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF, ONLINE = ON);'
        ELSE ''
    END AS SQL
FROM information_schema.referential_constraints RC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
LEFT JOIN sys.columns c ON ccu.Column_Name = C.name
    AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)
LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID
    AND c.column_id = ic.column_id
    AND index_column_id = 1
-- index found has the foreign key
-- as the first column 
LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID
    AND ic.index_Id = i.index_Id
WHERE I.name IS NULL
ORDER BY FK_table
    ,ParentTable
    ,ParentColumn;

Other References:

• Missing Indexes – Himanshu Patel
• Finding SQL Server Object Dependencies
• SQL Without Clustered Index
• Foreign Key Scripts

How to Reset Identity Value in SQL Server

In SQL Server, identity columns are used to automatically generate unique numeric values, commonly for primary key columns. However, situations may arise, such as after deleting records or resetting test data, where you need to reset the identity value so that new records start with a specific number again.

If you've ever deleted all the rows from a table but noticed that the identity column keeps incrementing from the last used value, this post will show you how to reset it using the DBCC CHECKIDENT command.

👉 Original post: How to Reset Identity Value

What is DBCC CHECKIDENT?

SQL Server provides the A DBCC CHECKIDENT command to manually reseed (reset) the identity value of a table. This is particularly useful after large deletions or when reloading data during development or ETL processes.

Syntax:

DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue);

• 'TableName': Your table name.
• RESEED: Instructs SQL Server to change the identity value.
• NewSeedValue: The number you want the next identity value to start from.

Example Scenario

Suppose you have a table called Employee With an identity column ID.

To reset the identity to start from 1 after deleting all records:

DELETE FROM Employee;
DBCC CHECKIDENT ('Employee', RESEED, 0);

⚠️ Note: If you reseed to 0, the next row inserted will receive ID = 1 (since identity starts from the seed + 1).

How to Check the Current Identity Value

To see the current identity seed without making changes:

DBCC CHECKIDENT ('Employee', NORESEED);

This will return the current seed and the next value to be used.

Important Considerations

• TRUNCATE vs DELETE: Using TRUNCATE TABLE resets the identity automatically, but DELETE does not.
• Existing Records: Be careful when reseeding if the table still contains rows—setting the seed too low can cause primary key violations.
• Permissions: Running DBCC CHECKIDENT may require ALTER permissions on the table.

When Should You Reset Identity Values?

• After deleting test or demo data in development environments.
• During staging or ETL operations, when reloading data.
• When maintaining a consistent identity sequence is necessary for reports or integrations.

Resetting identity values is a simple but powerful feature in SQL Server. With DBCC CHECKIDENT, you can take control over how identity values are managed in your tables.

For more details, you can refer to the following links:

Reassign the Identity of All Tables

The table has Identity Columns

Index Read/Write stats

 Index Read/Write stats

Here's a handy SQL query to help you analyze index usage in your database. This script provides valuable insights into how frequently your indexes are being read (through seeks, scans, and lookups) and updated, which can be crucial for performance tuning.

    SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName]
     ,i.[name] AS [IndexName]
     ,i.index_id
     ,s.user_seeks
     ,s.user_scans
     ,s.user_lookups
     ,s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads]
     ,s.user_updates AS [Writes]
     ,i.[type_desc] AS [Index Type]
     ,i.fill_factor AS [Fill Factor]
     ,i.has_filter
     ,i.filter_definition
     ,s.last_user_scan
     ,s.last_user_lookup
     ,s.last_user_seek
    FROM sys.indexes AS i WITH (NOLOCK)
    LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]
     AND i.index_id = s.index_id
     AND s.database_id = DB_ID()
    WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
    ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC
    OPTION (RECOMPILE);
  

This query focuses on user tables and orders the results by the total number of reads, making it easy to spot your most heavily accessed indexes. Understanding these metrics can guide you in optimizing your database performance.

What kind of performance issues are you currently troubleshooting?