List out all MDF and LDF File locations of SQL Sever Databases

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

List of all user databases in SQL Server

SELECT *
FROM   master.sys.databases
WHERE  Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0

Find last modified date of stored procedure or function in SQL server

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P' order by modify_date desc

Generate Script of All indexes in a SQL Server database

SELECT ' CREATE ' +
       CASE
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key))
                                                                            = 1 THEN
                                                                            ' DESC '
                                                                       ELSE
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal)
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           --AND I.Object_id = object_id('Person.Address') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 

How to find percentage of a database backup job done in SQL Server?

Script to find percentage of a database backup job is done

SELECT percent_complete
 ,*
FROM sys.dm_exec_requests
WHERE command IN (
  'RESTORE DATABASE'
  ,'BACKUP DATABASE'
  )

Elapsed time in Hours to complete the Job

SELECT command
 ,percent_complete
 ,'elapsed' = total_elapsed_time / 3600000.0
 ,'remaining' = estimated_completion_time / 3600000.0
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%'

Is it is possible to Limit the number of ErrorLog Files in SQL Server

Identify SQL Server Error Log File used by SQL Server Database Engine by Reading SQL Server Error Logs

SELECT SERVERPROPERTY('ErrorLogFileName')

Is it possible to limit the number of errorlog files less then 6 ?

Nope. Minimum number to configure is 6. We can delete all the archived errorlog files from server if we want but  ensure they are not required for auditing purposes and such.

we can set it to 6 and then create a scheduled job that simply calls

EXEC sp_cycle_errorlog

The more often we run this, the smaller error log files will have and will keep it to 6 error log files.

 clean up message by reviewing what messages are appearing. For example if it is "successful backup" messages you can enable trace flag 3226 globally for that instance and this will suppress those messages from being written all the time. Outside of that it is up to us to determine what is writing the message and figure out how to clean that up. An example of this that is most common is "failed login" messages for rogue service or application on a remote server.pplication on a remote server.

List of Permissions and their Description in SQL Server

Permission name	Description
ALTER	Permission to modify the object's definition
CONNECT	Permission to access the database or connect to the endpoint
DELETE	Permission to delete the object
EXECUTE	Permission to execute the stored procedure or the function
IMPERSONATE	Permission to take the identity of a principal, by the means of an EXECUTE AS command
INSERT	Permission to insert data into the table or view
REFERENCES	Permission to reference the object in a foreign key definition, or to declare a view or function WITH SCHEMABINDING referencing the object
SELECT	Permission to issue a SELECT command against the object or column
TAKE OWNERSHIP	Permission to become the owner of the object
UPDATE	Permission to update the data
VIEW DEFINITION	Permission to view the definition (structure) of the object

Script to find what Permissions apply to what class of securables in SQL Server

SELECT *
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY class_desc

Script to compare settings of two DB servers in SQL Server

USE master;
GO

EXEC sp_configure 'show advanced option'
 ,'1';

RECONFIGURE;

EXEC sp_configure;

Total number of error messages in SQL Server = 10548

SELECT message_id
 ,severity
 ,TEXT
FROM sys.messages
WHERE language_id = 1033;

Script to delete millions of records without increasing your log size in SQL Server

DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1

WHILE @continue = 1
BEGIN
 PRINT GETDATE()

 SET ROWCOUNT 10000     --Replace 10000 as required


 BEGIN TRANSACTION

 DELETE
 FROM dbo.Transactions
 WHERE TranDate IS NULL --Replace your delete script here

 SET @rowcount = @@rowcount

 COMMIT

 PRINT GETDATE()

 IF @rowcount = 0
 BEGIN
  SET @continue = 0
 END
END

Script to find port number of a SQL Server instance in Multiple ways

--Method 1
DECLARE @tcp_port NVARCHAR(5)

EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
 ,@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'
 ,@value_name = 'TcpPort'
 ,@value = @tcp_port OUTPUT

SELECT @tcp_port

--Method 2

sp_readerrorlog 1
 ,1
 ,'listening'
 ,'server'

--Method 3
SELECT SERVERNAME = CONVERT(NVARCHAR(128), SERVERPROPERTY('SERVERNAME'))
 ,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
 ,local_tcp_port AS 'PortNumber'
FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = @@SPID

Script to find number of rows in each partition in a partitioned table in SQL Server

SELECT t.NAME [table]
 ,p.rows
 ,p.partition_number
 ,v.boundary_id
 ,v.value
FROM sys.tables t
INNER JOIN sys.partitions p ON p.object_id = t.object_id
INNER JOIN sys.partition_range_values v ON v.boundary_id = p.partition_number
WHERE is_ms_shipped = 0
ORDER BY [table]

Script to split given range of values in to desired number of parts in SQL Server

DECLARE @min NUMERIC(18, 0)
DECLARE @max NUMERIC(18, 0)
DECLARE @parts NUMERIC(18, 0)

SELECT @min = 102201011472463
 ,-- Minimum value in your range of values
 @max = 102201354392808
 ,-- Maximum value in your range of values
 @parts = 3480 --Give number of parts

DECLARE @increment INT = (@max - @min) / @parts

WHILE @max >= @min
BEGIN
 DECLARE @newMin NUMERIC(18, 0) = @min + @increment

 PRINT convert(VARCHAR, @min) + ' - ' + convert(VARCHAR, @newMin)

 SELECT @min = @newMin + 1
END

Script to find backup size of all SQL Server databases backup size

USE msdb
GO

/* Please change device_type and backupset type combination as you need.
 
backupmediafamily device_type
 
2 = File
 
5 = Tape
 



backupset type
 
L = Log
 
I = Differential
 
D = Full
 
*/
SELECT (SUM(backup_size) + SUM(1536)) / 1024 / 1024 AS MBs
 ,(SUM(backup_size) + SUM(1536)) / 1024 / 1024 / 1024 AS GBs
FROM backupset
INNER JOIN (
 SELECT database_name
  ,MAX(backup_start_date) AS LastFullBackupDate
 FROM backupset
 WHERE media_set_id IN (
   SELECT media_set_id
   FROM backupmediafamily
   WHERE device_type = 2
   )
  AND type = 'D'
 GROUP BY database_name
 ) AS GetLastDate ON backupset.database_name = GetLastDate.database_name
 AND backupset.backup_start_date = GetLastDate.LastFullBackupDate

Script to find number of users connected to SQL Server

SELECT COUNT(*) AS ConnectionCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

List of services started automatically by Windows that you could consider stopping

# DHCP Client: On most servers, IP addresses are fixed and DHCP is not needed.

#DNS Client: This caches DNS names locally.

#Network Location Awareness: This collects and stores the configuration information

for the network and notifies the programs when this information is modified. You can't

disable it unless you use the Windows 2008 Advanced Firewall.

#Print Spooler: This loads the files to the memory for printing later. It can be disabled

if you don't print from the server.

#Windows Error Reporting Service: This allows errors to be reported when programs

stop working or responding, and allows existing solutions to be delivered. It can safely

be disabled.

#Windows Firewall: You can disable this service, if you are using a network firewall.

#Shell Hardware Detection: This activates AutoPlay for removable devices.

On Windows Server 2008 R2, this service stops when nobody is logged in,

to minimize risks. You can disable it.

Reference: Microsoft SQL Server 2012 Security

Cookbook

Script to list data types of all columns in a Database in SQL Server

SQL Server tables displays the data type of
each column inside the table. You can do this dozens of ways, but a popular method shown in
the following example joins the sys.objects table with the sys.columns table. There are two
functions that you may not be familiar with in the following code. The TYPE_NAME() function
translates the data type id into its proper name. To go the opposite direction, you could use the
TYPE_ID() function. The other function of note is SCHEMA_ID(), which is used to return the
identity value for the schema. This is useful primarily when you want to write reports against the
SQL Server metadata.

USE AdventureWorks
GO

SELECT o.NAME AS ObjectName
 ,c.NAME AS ColumnName
 ,TYPE_NAME(c.user_type_id) AS DataType
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.NAME = 'Department'
 AND o.Schema_ID = SCHEMA_ID('HumanResources')

Query to find total number of logical reads/physical reads done, when a given stored procedure is executed

SELECT ss.sum_execution_count

,t.TEXT

,ss.sum_total_elapsed_time

,ss.sum_total_worker_time

,ss.sum_total_logical_reads

,ss.sum_total_logical_writes

FROM (SELECT s.plan_handle

,SUM(s.execution_count) sum_execution_count

,SUM(s.total_elapsed_time) sum_total_elapsed_time

,SUM(s.total_worker_time) sum_total_worker_time

,SUM(s.total_logical_reads) sum_total_logical_reads

,SUM(s.total_logical_writes) sum_total_logical_writes

FROM sys.dm_exec_query_stats s

GROUP BY s.plan_handle

) AS ss

CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t

WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'

ORDER BY sum_total_logical_reads DESC



If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.

Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.

Changing the font color of a row dynamically using sp_send_dbmail

declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Test Data</H1>' +
N'<H4>TEST_sub_header</H4>' +
N'<H4>TEST_header_1</H4>' +
N'<table border="1">' +
N'<tr><th>place</th><th>Temperature</th>' +
N'</tr>' +
CAST ( ( select case when Temperature>40 then 'red' else 'black' end as "font/@color",
place as "font/td",'',
case when Temperature>40 then 'red' else 'black' end as "font/@color",
Temperature as "font/td"
from test_fontcolor
order by place
for xml path('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name='test',
@recipients='test@microsoft.com',
@from_address = 'storenote@microsoft.com',
@subject = 'Test Font Color',
@body = @tableHTML,
@body_format = 'HTML' ;