Useful SQL Joins diagram..

Tables row count, total space, used space and unused space

PROBLEM:
You want to find tables row count, total space (KB), used space (KB) and unused space (KB)

SOLUTION:
The following query returns the row count and size of tables in your database:

SELECT T.NAME AS TableName, P.ROWS AS RowCounts, SUM(A.TOTAL_PAGES) * 8 AS TotalSpaceKB, SUM(A.USED_PAGES) * 8 AS UsedSpaceKB, (SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)) * 8 AS UnusedSpaceKB
FROM SYS.TABLES T
INNER JOIN SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID
INNER JOIN SYS.PARTITIONS P ON I.OBJECT_ID = P.OBJECT_ID
AND I.INDEX_ID = P.INDEX_ID
INNER JOIN SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID
GROUP BY T.NAME,
         P.ROWS
ORDER BY T.NAME

The following query returns the size of your database (in megabytes):

SELECT sum(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Find all foreign keys that have cascade delete or update

PROBLEM:
How do you find all foreign keys that have cascade delete or update?

SOLUTION:

SELECT B.NAME AS TableName,
       A.NAME AS FKname,
       A.DELETE_REFERENTIAL_ACTION_DESC AS DeleteAction,
       A.UPDATE_REFERENTIAL_ACTION_DESC AS UpdateAction
FROM SYS.FOREIGN_KEYS A
JOIN SYS.TABLES B ON A.PARENT_OBJECT_ID = B.OBJECT_ID
WHERE A.DELETE_REFERENTIAL_ACTION=1
  OR A.UPDATE_REFERENTIAL_ACTION=1
ORDER BY 1,
         2

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

View database roles, logins, users and permissions

PROBLEM:
How do I list all my database roles, logins, users and permissions?

SOLUTION:

Who has access to my Databases?

SELECT DP.NAME AS UserName,
       DP.TYPE_DESC AS UserType,
       SP.NAME AS LoginName,
       SP.TYPE_DESC AS LoginType
FROM SYS.DATABASE_PRINCIPALS DP
JOIN SYS.SERVER_PRINCIPALS SP ON DP.PRINCIPAL_ID = SP.PRINCIPAL_ID

Server Roles (view which logins are tied to which server roles)

SELECT P1.NAME AS LoginName,
       P1.TYPE_DESC AS PrincipalType,
       P2.NAME AS RoleName,
       P2.TYPE_DESC AS RoleType
FROM SYS.SERVER_ROLE_MEMBERS ROLES
JOIN SYS.SERVER_PRINCIPALS P1 ON ROLES.MEMBER_PRINCIPAL_ID = P1.PRINCIPAL_ID
JOIN SYS.SERVER_PRINCIPALS P2 ON ROLES.ROLE_PRINCIPAL_ID = P2.PRINCIPAL_ID

Database Roles (determine which users are assigned to database roles)

SELECT P1.NAME AS UserName,
       P1.TYPE_DESC AS UserType,
       P2.NAME AS RoleName,
       P2.TYPE_DESC AS RoleType,
       P2.IS_FIXED_ROLE AS IsFixedRole
FROM SYS.DATABASE_ROLE_MEMBERS ROLES
JOIN SYS.DATABASE_PRINCIPALS P1 ON ROLES.MEMBER_PRINCIPAL_ID = P1.PRINCIPAL_ID
JOIN SYS.DATABASE_PRINCIPALS P2 ON ROLES.ROLE_PRINCIPAL_ID = P2.PRINCIPAL_ID

What can these users do? (indicate which users has specific permissions inside the current database)

SELECT DP.CLASS_DESC AS ClassDesc,
       DP.PERMISSION_NAME AS PermissionName,
       DP.STATE_DESC AS StateDesc,
       OBJECT_NAME(MAJOR_ID) AS ObjectName,
       GRANTEE.NAME AS GranteeName,
       GRANTOR.NAME AS GrantorName
FROM SYS.DATABASE_PERMISSIONS DP
JOIN SYS.DATABASE_PRINCIPALS GRANTEE ON DP.GRANTEE_PRINCIPAL_ID = GRANTEE.PRINCIPAL_ID
JOIN SYS.DATABASE_PRINCIPALS GRANTOR ON DP.GRANTOR_PRINCIPAL_ID = GRANTOR.PRINCIPAL_ID -- More detailed query..
WITH P AS
  ( SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,
           DP.PRINCIPAL_ID,
           DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,
           P.CLASS_DESC,
           OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,
           P.PERMISSION_NAME,
           P.STATE_DESC AS PERMISSION_STATE_DESC
   FROM SYS.DATABASE_PERMISSIONS P
   INNER JOIN SYS.DATABASE_PRINCIPALS DP ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID)
SELECT P.PRINCIPAL_NAME,
       P.PRINCIPAL_TYPE_DESC,
       P.CLASS_DESC,
       P.[OBJECT_NAME],
       P.PERMISSION_NAME,
       P.PERMISSION_STATE_DESC,
       CAST(NULL AS SYSNAME) AS ROLE_NAME
FROM P
WHERE PRINCIPAL_TYPE_DESC <> 'DATABASE_ROLE'
UNION
SELECT R.MEMBER_PRINCIPAL_NAME,
       R.PRINCIPAL_TYPE_DESC,
       P.CLASS_DESC,
       P.OBJECT_NAME,
       P.PERMISSION_NAME,
       P.PERMISSION_STATE_DESC,
       R.ROLE_NAME
FROM P
RIGHT OUTER JOIN
  ( SELECT ROLE_PRINCIPAL_ID,
           DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,
           MEMBER_PRINCIPAL_ID,
           USER_NAME(MEMBER_PRINCIPAL_ID) AS MEMBER_PRINCIPAL_NAME,
           USER_NAME(ROLE_PRINCIPAL_ID) AS ROLE_NAME
   FROM SYS.DATABASE_ROLE_MEMBERS R
   INNER JOIN SYS.DATABASE_PRINCIPALS DP ON R.MEMBER_PRINCIPAL_ID = DP.PRINCIPAL_ID) R ON R.ROLE_PRINCIPAL_ID = P.PRINCIPAL_ID
ORDER BY 1

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

The most useful shortcut key in SQL Server management studio

PROBLEM:
What is the most useful shortcut key in SQL Server management studio?

SOLUTION:
In the new query window, mark your table name and press ALT - F1

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Database Engine Tuning Advisor Error

PROBLEM:
When trying to open SQL Server Database Engine Tuning Advisor you get an error: "Failed to connect to an IPC Port: The system cannot find the file specified.(mscorlib)"

SOLUTION:
Kill DTASHELL.exe in the task manager

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!