To find SQL Version,Edition & Instance Name in SQL 2008,2012,2014,etc

The below query will give the SQL Product Full Specification details.

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Product Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

How to find Indexes on a Table In SQL Server

Using system stored procedure sp_helpindex, system catalog views like sys.indexes or sys.index_columns methods. 

1. Find Indexes On A Table Using SP_HELPINDEX

sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. This is the easiest method to find the indexes in a table. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.

EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
GO

Missing Index Script in SQL Server Database 2016

Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance. In this blog post we will discuss about Missing Index. Here is the script from my script bank, which I use to identify missing indexes on any database.

Last Used Database in SQL Server

In SQL Server 2005, can you easily determine the last time someone queried a database.

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id

GROUP BY d.name

SQL Relay

SQL RELAY

How to Find Free Log Space in SQL Server?

This post will show you how to monitor free log space in SQL Server.

Method 1: Using SQLPERF

DBCC SQLPERF ('LOGSPACE')
GO

Method 2: sys.dm_db_log_space_usage
  
SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage;

How to Validate Email Address in SQL Server?

How to Validate Email Address in SQL Server-Let us create a column called Email Address in the table named Contacts. Once we create the table, we will insert few valid and invalid email address in it.