Scrip to find Schema level size information in SQL Server

--Script to know number of tables, row count, data size, index size, used size
BEGIN TRY
 SELECT
  --(row_number() over(order by a3.name, a2.name))%2 as l1,
  a3.NAME AS [schemaname]
  ,count(a2.NAME) AS NumberOftables
  ,sum(a1.rows) AS row_count
  ,sum((a1.reserved + ISNULL(a4.reserved, 0)) * 8) AS reserved
  ,sum(a1.data * 8) AS data
  ,sum((
    CASE 
     WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data
      THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
     ELSE 0
     END
    ) * 8) AS index_size
  ,sum((
    CASE 
     WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
      THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
     ELSE 0
     END
    ) * 8) AS unused
 FROM (
  SELECT ps.object_id
   ,SUM(CASE 
     WHEN (ps.index_id < 2)
      THEN row_count
     ELSE 0
     END) AS [rows]
   ,SUM(ps.reserved_page_count) AS reserved
   ,SUM(CASE 
     WHEN (ps.index_id < 2)
      THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
     ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
     END) AS data
   ,SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  GROUP BY ps.object_id
  ) AS a1
 LEFT JOIN (
  SELECT it.parent_id
   ,SUM(ps.reserved_page_count) AS reserved
   ,SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (
    202
    ,204
    )
  GROUP BY it.parent_id
  ) AS a4 ON (a4.parent_id = a1.object_id)
 INNER JOIN sys.all_objects a2 ON (a1.object_id = a2.object_id)
 INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
 WHERE a2.type <> 'S'
  AND a2.type <> 'IT'
 GROUP BY a3.NAME
 ORDER BY a3.NAME
END TRY

BEGIN CATCH
 SELECT - 100 AS l1
  ,1 AS schemaname
  ,ERROR_NUMBER() AS tablename
  ,ERROR_SEVERITY() AS row_count
  ,ERROR_STATE() AS reserved
  ,ERROR_MESSAGE() AS data
  ,1 AS index_size
  ,1 AS unused
END CATCH

Script to drop all Foreign keys and recreate them

--Create Table and Save Foreign Keys in a Table
--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

CREATE TABLE fklist (
 RowId INT PRIMARY KEY IDENTITY(1, 1)
 ,ForeignKeyConstraintName NVARCHAR(200)
 ,ForeignKeyConstraintTableSchema NVARCHAR(200)
 ,ForeignKeyConstraintTableName NVARCHAR(200)
 ,ForeignKeyConstraintColumnName NVARCHAR(200)
 ,PrimaryKeyConstraintName NVARCHAR(200)
 ,PrimaryKeyConstraintTableSchema NVARCHAR(200)
 ,PrimaryKeyConstraintTableName NVARCHAR(200)
 ,PrimaryKeyConstraintColumnName NVARCHAR(200)
 )

INSERT INTO fklist (
 ForeignKeyConstraintName
 ,ForeignKeyConstraintTableSchema
 ,ForeignKeyConstraintTableName
 ,ForeignKeyConstraintColumnName
 )
SELECT U.CONSTRAINT_NAME
 ,U.TABLE_SCHEMA
 ,U.TABLE_NAME
 ,U.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE fklist
SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE fklist
SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA
 ,PrimaryKeyConstraintTableName = TABLE_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE fklist
SET PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM fklist T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

SELECT *
FROM fklist

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END

-----------------------------------------------------------------------------------

--Drop Foreign Keys









SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
 FROM fklist
 WHERE RowId = @counter

 --exec ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')
 PRINT ('alter table [' + @schema + '].[' + @table + '] drop constraint [' + @constraint + ']')

 SET @counter = @counter - 1
END


--Recreate Foreign Keys
SET NOCOUNT ON

DECLARE @counter INT
DECLARE @constraint NVARCHAR(200)
DECLARE @constraint_col NVARCHAR(200)
DECLARE @schema NVARCHAR(200)
DECLARE @pk_schema NVARCHAR(200)
DECLARE @table NVARCHAR(200)
DECLARE @pk_table NVARCHAR(200)
DECLARE @pk_col NVARCHAR(200)

--DROP CONSTRAINT:
--set counter variable to number of rows inserted
SELECT @counter = MAX(RowId)
FROM fklist

--exec alter table to drop each constriant
WHILE @counter > 0
BEGIN
 SELECT @constraint = ForeignKeyConstraintName
  ,@schema = ForeignKeyConstraintTableSchema
  ,@table = ForeignKeyConstraintTableName
  ,@constraint_col = ForeignKeyConstraintColumnName
  ,@pk_schema = PrimaryKeyConstraintTableSchema
  ,@pk_table = PrimaryKeyConstraintTableName
  ,@pk_col = PrimaryKeyConstraintColumnName
 FROM fklist
 WHERE RowId = @counter

 PRINT ('ALTER TABLE [' + @schema + '].[' + @table + '] ADD CONSTRAINT ' + @constraint + ' FOREIGN KEY(' + @constraint_col + ') REFERENCES [' + @pk_schema + '].[' + @pk_table + '](' + @pk_col + ')')

 SET @counter = @counter - 1
END

Fastest way to row count all tables in a Database SQL Server

--Full Database
SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.NAME) AS [TableName]
 ,SUM(sPTN.Rows) AS [RowCount]
FROM sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id
WHERE sOBJ.type = 'U'
 AND sOBJ.is_ms_shipped = 0x0
 AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY sOBJ.schema_id
 ,sOBJ.NAME
ORDER BY [TableName]
GO

-----------------------------------------------------------
-- For Individual Schema**
SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.NAME) AS [TableName]
 ,SUM(sPTN.Rows) AS [RowCount]
FROM sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id
WHERE sOBJ.type = 'U'
 AND sOBJ.is_ms_shipped = 0x0
 AND index_id < 2 -- 0:Heap, 1:Clustered
 AND QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) = '[YOURSCHEMANAMEHERE]'
GROUP BY sOBJ.schema_id
 ,sOBJ.NAME
ORDER BY [TableName]
GO

Change the ownership of SQL Server agent jobs

The jobs may start failing when their AD accounts are removed. If the users have left it is better to change the job owner to a different account.
To ensure such issues wont happen, it is better to create a generic AD account with only necessary permissions and use it exclusively for this purpose.

List down all the jobs which are tied to the users and share it with all the respective project managers and technical architects and get a approval .
Decide on a common user account and create new one .

Script to get elapsed time from start of current day in SQL Server

How to get elapsed time in Seconds, Minutes, Hours from start of current day?

SELECT DATEDIFF(SECOND, 0, Cast(GETDATE() AS TIME)) -- In Seconds

SELECT DATEDIFF(MINUTE, 0, Cast(GETDATE() AS TIME)) -- In Minutes

SELECT DATEDIFF(HOUR, 0, Cast(GETDATE() AS TIME)) -- In Hours

Checking Email log in SQL Server

--SENT MESSAGES LOG
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC

--FAILED MESSAGES LOG
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

--ALL MESSAGES – REGARDLESS OF STATUS
SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC

SQL Server Endpoint

Configuring endpoint security

A SQL Server endpoint is a door opened from or to SQL Server. Everything that can go from your SQL Server to the network, or from the network to SQL Server, goes through an endpoint. Endpoints can be system or user-defined. The system endpoints allow the usual T-SQL connections to SQL Server to send queries.
The endpoints are defined for a specific protocol, which were either HTTP or TCP in the
previous versions. In SQL Server 2012, the HTTP endpoints previously used for the native
web services feature are removed. Now you only have TCP endpoints.
You can create a user-defined endpoint for three purposes: TCP requests, service broker, or
database mirroring. Here we will talk about the first one, which is useful to set up a dedicated
and secured connection to SQL Server for administrative purposes or distant access.

How to do it...

To configure endpoint security, follow these steps:
1. In a SQL query window, type the following T-SQL command:
CREATE ENDPOINT myTSQLEndpoint
STATE = started
AS TCP (
LISTENER_PORT = 8080,
LISTENER_IP = (127.0.0.1)
)
FOR TSQL ();
2. You will receive a message saying that it will cause the revocation of any Public
connect permissions on the TSQL default TCP endpoint. This effectively means that
all logins will lose their permission to connect to SQL Server through the default
T-SQL endpoint. If you still want to allow connection permissions to the default TCP
endpoint, issue the following command:
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
3. You can view the state of the endpoints with this command:
SELECT * FROM sys.tcp_endpoints;
Chapter 1
45
4. You can start or stop the endpoints with the ALTER ENDPOINT command.
For example, you can stop the default TCP endpoint as follows:
ALTER ENDPOINT [TSQL Default TCP]
STATE = STOPPED;
The state we just creat@ed will remain even after a service restart.

How it works...

When SQL Server is installed, a system endpoint is created for each network protocol used
in SQL Server. The permission to access these endpoints is given to the Public server role.
Every login declared in SQL Server is a member of this role, and permissions on the Public
server role can be changed, unlike other fixed server roles. You can grant, revoke, or deny
permissions to connect to an endpoint to all the logins through the Public role, or to specific
logins by revoking CONNECT permissions to the Public role, and by granting specific privileges
as follows:
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [a_specific_login];
If you want to allow connections to SQL Server from only a specific client IP address, you can
stop the default endpoint, or deny access to it, and create a user-defined T-SQL endpoint, with
a client IP address and a TCP port.
Stopping default endpoints has the same effect as disabling them in SQL Server
Configuration Manager.

There's more...

In SQL Server 2012, you can create user-defined server roles. We will detail this functionality
later. This interests us for now, because a server role could be used to grant CONNECT
permissions on an endpoint to a group of logins.
The following code creates a user-defined server role, adds a login as a member, and grants
the CONNECT privilege on the default TCP endpoint to the role:
USE [master];
CREATE SERVER ROLE [TCPRole];
ALTER SERVER ROLE [TCPRole] ADD MEMBER [my_login];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [TCPRole];

Reference: Microsoft SQL Server 2012 Security

Cookbook