Wednesday, January 22, 2020

Tables with at least two triggers

SQL Query to Find Tables with Multiple Triggers

This SQL query is designed to find tables in a database that have more than one trigger associated with them.

DECLARE @min_count INT;
SET @min_count = 2;

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
  SELECT 1 FROM [sys].triggers tr
  WHERE tr.parent_id = t.[object_id]
  GROUP BY tr.parent_id
  HAVING COUNT(*) >= @min_count
);

This query checks the system tables and triggers to identify those tables that contain two or more triggers. The @min_count variable is used to set the threshold for the number of triggers a table must have.

Tables contains XML columns

How to Retrieve Table Names with XML Columns in SQL Databases

When working with SQL databases, it's common to need to find which tables contain XML columns. This can be especially useful for performing certain operations or analyzing database structure.

To help with this, here is a SQL query that can retrieve the table names, along with their schema, where XML columns are present.

SQL Query:


SELECT [Table] = s.name + N'.' + t.name
FROM sys.tables t
   INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
   SELECT 1 FROM sys.columns c
   WHERE c.[object_id] = t.[object_id]
   AND c.system_type_id = 241 -- 241 = xml
);

This SQL query works by joining the sys.tables and sys.schemas system views to find tables and their schemas. It then filters the tables where any column has a system type ID of 241, which corresponds to the XML data type in SQL Server.

You can learn more about working with XML data in SQL by checking out these related resources:

Sunday, January 5, 2020

Find TOP 50 unused indexes in a database

Identifying Unused Non-Clustered Indexes in SQL Server

Over time, SQL Server databases accumulate indexes—some of which may no longer be in active use. Unused or rarely used non-clustered indexes can consume unnecessary resources and slow down database maintenance tasks. It's good practice to review and clean up such indexes periodically, especially if the SQL Server instance has been running continuously for a long period without a restart (which resets index usage stats).

Query to Find Unused Non-Clustered Indexes

The following SQL query helps identify non-clustered indexes that haven’t been utilized much. It lists the top 50 indexes based on the lowest total usage (seeks, scans, and lookups), providing insight into which ones may be redundant.


SELECT TOP 50
     o.[Name] AS [ObjectName],
     i.[Name] AS IndexName,
     i.index_id AS IndexID,
     dm_ius.user_seeks AS UserSeek,
     dm_ius.user_scans AS UserScans,
     dm_ius.user_lookups AS UserLookups,
     dm_ius.user_updates AS UserUpdates,
     p.TableRows,
     'DROP INDEX ' + QUOTENAME(i.[Name])
     + ' ON ' + QUOTENAME(s.[Name]) + '.'
     + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'Drop statement'
FROM [sys].dm_db_index_usage_stats dm_ius
INNER JOIN [sys].indexes i ON i.index_id = dm_ius.index_id 
     AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN [sys].objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN (
     SELECT SUM(p.ROWS) AS TableRows, p.index_id, p.OBJECT_ID
     FROM [sys].partitions p
     GROUP BY p.index_id, p.OBJECT_ID
) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
  AND dm_ius.database_id = DB_ID()
  AND i.type_desc = 'nonclustered'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC;
GO

This query helps identify potential candidates for index cleanup by showing:

The object and index names
Usage statistics (seeks, scans, lookups, updates)
Number of table rows
A ready-to-use DROP INDEX statement for each listed index

Before dropping any indexes, ensure they’re truly unnecessary by reviewing application requirements or running further performance tests.

Friday, January 3, 2020

Get current database files information

Understanding SQL Server Database Files and Filegroups

When working with SQL Server, it's crucial to understand how database files and filegroups are structured. This knowledge helps you manage your database's storage efficiently and optimize performance. Below is a useful SQL query that provides detailed information about your database files, including their size, available space, and growth settings.


SELECT f.name AS [File Name]
 ,f.physical_name AS [Physical Name]
 ,CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [Total Size in MB]
 ,CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL(15, 2)) AS [Available Space In MB]
 ,f.[file_id]
 ,fg.name AS [Filegroup Name]
 ,f.is_percent_growth
 ,f.growth
 ,fg.is_default
 ,fg.is_read_only
 ,fg.is_autogrow_all_files
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
ORDER BY f.[file_id]
OPTION (RECOMPILE);

This query gives you a comprehensive overview of your database's physical files. You can see their **logical names**, **physical paths**, **total allocated size in MB**, and the **remaining free space**. It also displays details about the **filegroups** they belong to, such as their **growth settings** and whether they are **read-only** or **autogrow all files**.

For more in-depth information, you can refer to the official Microsoft documentation on filegroups and the sys.database_files system catalog view.

SQL Server help