What is dbo in SQL Server?

dbo stands for DataBase Owner. It is a special database user that maps to the owner of the
database. When created, a database is assigned an owner, which is the login that created it.
You can query the database owner using one of the following queries:

SELECT SUSER_SNAME(owner_sid)
 ,NAME
FROM sys.databases;

-- or :
SELECT SUSER_SNAME(sid)
FROM sys.database_principals
WHERE principal_id = USER_ID('dbo');

This login is automatically mapped to the dbo special user, and thus is granted all
permissions in the database. You can change the owner of a database by using the
following command:

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;

This will change the owner of the marketing database to the sa login.

SQl Server "How is My Memory Being used" Performance Counters"

Reference: http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

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

Upgrading the Integration Services in SQL Server from one edition to other

Upgrade Integration Services

SQL Server 2014

If SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) is currently installed on your computer, you can upgrade to SQL Server 2014 Integration Services (SSIS).

When you upgrade to SQL Server 2014 Integration Services (SSIS) on a machine that has one of these earlier versions of Integration Services installed, SQL Server 2014 Integration Services (SSIS) is installed side-by-side with the earlier version.

With this side-by-side install, multiple versions of dtexec utility are installed. To ensure that you run the correct version of the utility, at the command prompt run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\<version>\DTS\Binn). For more information about dtexec, see dtexec Utility.

Before Upgrading Integration Services

We recommended that you run Upgrade Advisor before you upgrade to SQL Server 2014. Upgrade Advisor reports issues that you might encounter if you migrate existing Integration Services packages to the new package format that SQL Server 2014 uses. For more information, see Use Upgrade Advisor to Prepare for Upgrades.

Upgrading Integration Services

You can upgrade by using one of the following methods:

• Run SQL Server 2014 Setup and select the option to Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2, or SQL Server 2012.
• Run setup.exe at the command prompt and specify the /ACTION=upgrade option. For more information, see the section, "Installation Scripts for Integration Services," in Install SQL Server 2014 from the Command Prompt.

You cannot use upgrade to perform the following actions:

• Reconfigure an existing installation of Integration Services.
• Move from a 32-bit to a 64-bit version of SQL Server or from a 64-bit version to a 32-bit version.
• Move from one localized version of SQL Server to another localized version.

When you upgrade, you can upgrade both Integration Services and the Database Engine, or just upgrade the Database Engine, or just upgrade Integration Services. If you upgrade only the Database Engine, SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) remains functional, but you do not have the functionality of SQL Server 2014 Integration Services (SSIS). If you upgrade only Integration Services, SQL Server 2014 Integration Services (SSIS) is fully functional, but can only store packages in the file system, unless an instance of the SQL Server 2014 Database Engine is available on another computer.

Upgrading Both Integration Services and the Database Engine to SQL Server 2014

This section describes the effects of performing an upgrade that has the following criteria:

• You upgrade both Integration Services and an instance of the Database Engine to SQL Server 2014.
• Both Integration Services and the instance of the Database Engine are on the same computer.

What the Upgrade Process Does

The upgrade process does the following tasks:

• Installs the SQL Server 2014 Integration Services (SSIS) files, service, and tools (Management Studio and SQL Server Data Tools). When there are multiple instances of SQL Server 2005 or SQL Server 2008 on the same computer, the first time you upgrade any of the instances to SQL Server 2014, the SQL Server 2014 Integration Services (SSIS) files, service, and tools are installed.
• Upgrades the instance of the SQL Server 2005 or the SQL Server 2008 Database Engine to the SQL Server 2014 version.
• Moves data from the SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) system tables to the SQL Server 2014 Integration Services (SSIS) system tables, as follows:
• Moves packages without change from the msdb.dbo.sysdtspackages90 system table to the msdb.dbo.sysssispackages system table.
• Moves folder metadata from the msdb.sysdtsfolders90 system table to the msdb.sysssisfolders system table.
• Moves log data from the msdb.sysdtslog90 system table to the msdb.sysssislog system table.
• Removes the msdb.sysdts*90 system tables and the stored procedures that are used to access them after moving the data to the new msdb.sysssis* tables. However, upgrade replaces the sysdtslog90 table with a view that is also named sysdtslog90. This new sysdtslog90 view exposes the new msdb.sysssislog system table. This ensures that reports based on the log table continue to run without interruption.
• To control access to packages, creates three new fixed database-level roles: db_ssisadmin, db_ssisltduser, and db_ssisoperator. The SQL Server 2005 Integration Services roles of db_dtsadmin, db_dtsltduser, and db_dtsoperator are not removed, but are made members of the corresponding new roles.
• If the SSIS package store (that is, the file system location managed by the Integration Services service) is the default location under \SQL Server\90, \SQL Server\100, or \SQL Server\110 moves those packages to the new default location under \SQL Server\120.
• Updates the Integration Services service configuration file to point to the upgraded instance of the Database Engine.

What the Upgrade Process Does Not Do

The upgrade process does not do the following tasks:

• Does not remove the SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) service.
• Does not migrate existing Integration Services packages to the new package format that SQL Server 2014 uses. For information about how to migrate packages, see Upgrade Integration Services Packages.
• Does not move packages from file system locations, other than the default location, that have been added to the service configuration file. If you have previously edited the service configuration file to add more file system folders, packages that are stored in those folders will not be moved to a new location.
• In SQL Server Agent job steps that call the dtexec utility (dtexec.exe) directly, does not update the file system path for the dtexec utility. You have to edit these job steps manually to update the file system path to specify the SQL Server 2014 location for the dtexec utility.

What You Can Do After Upgrading

After the upgrade process finishes, you can do the following tasks:

• Run SQL Server Agent jobs that run packages.
• Use Management Studio to manage Integration Services packages that are stored in an instance of SQL Server 2008 or SQL Server 2014. You need to modify the service configuration file to add the instance of SQL Server 2008 to the list of locations managed by the service.
• Identify the version of packages in the msdb.dbo.sysssispackages system table by checking the value in the packageformat column. The table has a packageformat column that identifies the version of each package. A value of 2 in the packageformat column indicates a SQL Server 2005 Integration Services (SSIS) package; a value of 3 indicates a SQL Server 2008 Integration Services (SSIS) package. Until you migrate packages to the new package format, the value in the packageformat column does not change.
• You cannot use the SQL Server 2005 or SQL Server 2008 tools to design, run, or manage Integration Services packages. The SQL Server 2005 and SQL Server 2008 tools include the respective versions of SQL Server Data Tools (SSDT), the SQL Server Import and Export Wizard, and the Package Execution Utility (dtexecui.exe). The upgrade process does not remove the SQL Server 2005 or SQL Server 2008 tools. However, you will not able to use these tools to continue to work with SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) packages on a server that has been upgraded.
• By default, in an upgrade installation, Integration Services is configured to log events that are related to the running of packages to the Application event log. This setting might generate too many event log entries when you use the Data Collector feature of SQL Server 2014. The events that are logged include EventID 12288, "Package started," and EventID 12289, "Package finished successfully." To stop logging these two events to the Application event log, open the registry for editing. Then in the registry, locate the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS node, and change the DWORD value of the LogPackageExecutionToEventLog setting from 1 to 0.

Upgrading only the Database Engine to SQL Server 2014

This section describes the effects of performing an upgrade that has the following criteria:

• You upgrade only an instance of the Database Engine. That is, the instance of the Database Engine is now an instance of SQL Server 2014, but the instance of Integration Services and the client tools are from SQL Server 2005 or SQL Server 2008.
• The instance of the Database Engine is on one computer, and Integration Services and the client tools are on another computer.

What You Can Do After Upgrading

The system tables that store packages in the upgraded instance of the Database Engine are not the same as those used in SQL Server 2005 or SQL Server 2008. Therefore, the SQL Server 2005 or SQL Server 2008 versions of Management Studio and SQL Server Data Tools cannot discover the packages in the system tables on the upgraded instance of the Database Engine. Because these packages cannot be discovered, there are limitations on what you can do with those packages:

• You cannot use the SQL Server 2005 or SQL Server 2008 tools, Management Studio and SQL Server Data Tools, on other computers to load or manage packages from the upgraded instance of the Database Engine. 
• You cannot use SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) on other computers to run packages that are stored in msdb on the upgraded instance of the Database Engine.
• You cannot use SQL Server Agent jobs on SQL Server 2005 or SQL Server 2008 computers to run SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) packages that are stored in the upgraded instance of the Database Engine.

Reference :  http://msdn.microsoft.com/en-us/library/cc879336.aspx

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;

Restoring Orphaned MDF File in SQL Server

You can attach the database using CREATE DATABASE FOR ATTACH_REBUILD_LOG.

http://msdn.microsoft.com/en-us/library/ms176061.aspx

If that doesn't work, you can try the undocumented FOR ATTACH_FORCE_REBUILD_LOG.

CREATE DATABASE < YOURDBNAME > ON (FILENAME = 'D:\<YOURMDFNAME>.mdf')
FOR ATTACH_FORCE_REBUILD_LOG

From Management Studio

Attaching the .mdf file through the management console, without a .ldf file

1. right click "Databases" and select attach.
2. Click on "add" and select the .mdf file, click OK.
3. In the details panel select the .dlf file, click remove button.
4. Click the main OK button.

Total number of error messages in SQL Server = 10548

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

The MSSQLServer service terminated unexpectedly. Check the SQL Server error log and Windows System and Application event logs for possible causes.

Solution: 

Oops! You might be running a very old build of SQL Server. Immediately check for new release of Service Packs, I would recommend installing recent service pack and then the latest cumulative update package.


If the Service pack installation didn't solve your Issue

For additional help, Open a case with Microsoft immediately.

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

Script to Load SQL Profiler trace file data into a table in SQL Server

SELECT *
FROM fn_get_audit_file('E:\Microsoft SQL Server\SQL Server Auditing\Audit-20120828-143913_CDE79597-4A8E-4C51-ACBB-35C0F8278C85_0_129906638124380000.sqlaudit', DEFAULT, DEFAULT)

Script to find date and details when database was restored in SQL Server

USE msdb;

SELECT DBRestored = destination_database_name
 ,RestoreDate = restore_date
 ,SourceDB = b.database_name
 ,SourceFile = physical_name
 ,BackupDate = backup_start_date
FROM RestoreHistory h
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate

Script to find Windows authentication mode/ Mixed authentication mode in SQL Server using TSQL

DECLARE @AuthenticationMode INT

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
 ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
 ,N'LoginMode'
 ,@AuthenticationMode OUTPUT

SELECT CASE @AuthenticationMode
  WHEN 1
   THEN 'Windows Authentication'
  WHEN 2
   THEN 'Windows and SQL Server Authentication'
  ELSE 'Unknown'
  END AS [Authentication Mode]

Script to find number of users connected to SQL Server

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

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