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

Find what script is being run by specific SPID number

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 53
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

SQL Script to find all Date/time columns in database

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

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%'

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Try this Solution : 

Nothing could be found in SQL Error logs.Check the service account of SQL Server, find out whether it has enough permissions to run SQLServer. If you are not able to change the Service acccounts permissions immediately and wants to come out of this Service Pack Installtion errors.  Change Service account for SQL Server to Local System. SQL Server DB Engine Service will be started and the build number will be updated too. Can revert your SQL Servers Service account back to Original from Local System.

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 convert a database to contained database in SQL Server

What is Contained Database in SQL Server 2012?

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.
Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
All metadata are defined using the same collation.
User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.


To Set-up  contained databases  is a simple process which involves the following being carried out within SSMS:-

sp_configure 'contained database authentication'
 ,1
GO

RECONFIGURE

We can convert a non contained database to a contained database simply by setting its CONTAINMENT property,

USE [master]
GO

ALTER DATABASE [marketing]

SET CONTAINMENT = PARTIAL;

The users mapped to SQL logins can be converted to contained database users, using the sp_migrate_user_to_contained system
procedure 

SELECT 'EXEC sp_migrate_user_to_contained @username = N''' + dp.NAME + ''',
@rename = N''keep_name'',
@disablelogin = N''do_not_disable_login'' ;'
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
 AND sp.is_disabled = 0;

This code returns execute statements copy it and execute

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.


If you move a non-contained database from one server to another, by means of backup/
restore or detach/attach, then there is a chance that your SQL users will become
orphaned, meaning that they will have no corresponding login. As the mapping between logins
and users is done by the SID, if a login is present on the destination instance with the same
name but another SID, then the user will not recognize it and will be orphaned.
If you are moving the database to another server in the same domain, the
user to login mapping problem occurs only with SQL logins, because the SID
used for Windows logins is the same as the domain SID set in Active Directory.
Thus it is the same on every instance where this login is created.

Script to obtain SQL feature list from the command line

The components for SQL Server 2012 are divided by server and then management tools. You can get the server level components by using a PowerShell command like:

Get - Service * SQL 

To get the management tools would require either registry search for the uninstall list or you can query the WMI class win32_product:

get - wmiobject win32_product |
WHERE {$_.NAME - match "SQL" -
 AND $_. vendor - eq "Microsoft Corporation" } |

SELECT NAME
 ,version

Reference:  http://dba.stackexchange.com/questions/77016/how-to-obtain-sql-feature-list-from-the-command-line

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;

Script to generate drop all tables statements in a database in SQL Server

SELECT 'Drop table ' + NAME
FROM sys.objects
WHERE schema_name(schema_id) = 'dbo'
 AND type = 'u'

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 sync up logins and database users in SQL Server

USE DATABASE
GO

EXEC sp_change_users_login 'update_one'
 ,'databse username'
 ,'loginname'
GO

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 list out all recently ran queries in SQL Server

SELECT deqs.last_execution_time AS [Time]
 ,dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

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 list all SQL Server instance names in a server

SET NOCOUNT ON

DECLARE @CurrID INT
 ,@ExistValue INT
 ,@MaxID INT
 ,@SQL NVARCHAR(1000)
DECLARE @TCPPorts TABLE (
 PortType NVARCHAR(180)
 ,Port INT
 )
DECLARE @SQLInstances TABLE (
 InstanceID INT identity(1, 1) NOT NULL PRIMARY KEY
 ,InstName NVARCHAR(180)
 ,Folder NVARCHAR(50)
 ,StaticPort INT NULL
 ,DynamicPort INT NULL
 ,Platform INT NULL
 );
DECLARE @Plat TABLE (
 Id INT
 ,NAME VARCHAR(180)
 ,InternalValue VARCHAR(50)
 ,Charactervalue VARCHAR(50)
 )
DECLARE @Platform VARCHAR(100)

INSERT INTO @Plat
EXEC xp_msver platform

SELECT @Platform = (
  SELECT 1
  FROM @plat
  WHERE charactervalue LIKE '%86%'
  )

IF @Platform IS NULL
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 64
END
ELSE
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 32
END

DECLARE @Keyexist TABLE (Keyexist INT)

INSERT INTO @Keyexist
EXEC xp_regread 'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

SELECT @ExistValue = Keyexist
FROM @Keyexist

IF @ExistValue = 1
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

UPDATE @SQLInstances
SET Platform = 32
WHERE Platform IS NULL

SELECT @MaxID = MAX(InstanceID)
 ,@CurrID = 1
FROM @SQLInstances

WHILE @CurrID <= @MaxID
BEGIN
 DELETE
 FROM @TCPPorts

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 UPDATE SI
 SET StaticPort = P.Port
  ,DynamicPort = DP.Port
 FROM @SQLInstances SI
 INNER JOIN @TCPPorts DP ON DP.PortType = 'TCPDynamicPorts'
 INNER JOIN @TCPPorts P ON P.PortType = 'TCPPort'
 WHERE InstanceID = @CurrID;

 SET @CurrID = @CurrID + 1
END

SELECT serverproperty('ComputerNamePhysicalNetBIOS') AS ServerName
 ,InstName
 ,StaticPort
 ,DynamicPort
 ,Platform
FROM @SQLInstances

SET NOCOUNT OFF

Script to list out all object level permissions in SQL Server

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) + CASE 
  WHEN cl.column_id IS NULL
   THEN SPACE(0)
  ELSE '(' + QUOTENAME(cl.NAME) + ')'
  END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Object Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
 AND cl.[object_id] = PERM.major_id
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Database Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE PERM.major_id = 0
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC