How to get the size of all tables in MSSQL

To get the size of all tables using MSSQL you can use the snippet below.

Sample MSSQL

SELECT 
    tab.NAME AS [Tablename],
    s.Name AS [Schema Name],
    part.rows AS [Count of Rows],
    SUM(a.total_pages) * 8 AS [Total Space in KB], 
    SUM(a.used_pages) * 8 AS [Used Space in KB], 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8  AS [Unused Space in KB]
FROM sys.tables tab
INNER JOIN sys.indexes idx ON tab.OBJECT_ID = idx.object_id
INNER JOIN sys.partitions part ON idx.object_id = part.OBJECT_ID AND idx.index_id = part.index_id
LEFT OUTER JOIN sys.schemas s ON tab.schema_id = s.schema_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
WHERE tab.NAME NOT LIKE 'dt%' AND tab.is_ms_shipped = 0 AND idx.OBJECT_ID > 255 
GROUP BY tab.Name, s.Name, part.Rows
ORDER BY 4 DESC

Please rate this snippet

How to get a list of all tables in Oracle

To get a list of all tables in Oracle you can use the following query.

SELECT owner, table_name FROM dba_tables

if your user does not have sufficient access rights to query dba_tables, you can try to query all_tables which will only show you the tables you are allowed to see.

SELECT owner, table_name FROM all_tables

Please rate this snippet

How to list all Databases in MSSQL

To list all Databases in MSSQL you can use the following snippet.

Sample MSSQL

SELECT name FROM sys.databases

SQL Server Version newer than MS SQL 2000 also support these StoredProcedures.

EXEC sp_databases

or

EXEC sp_helpdb

Please rate this snippet

OpenIfClosed DBConnection extension method for C# and VB.NET

This snippet will allow you to use the OpenIfClosed DBConnection extension method for C# and VB.NET.

Sample C#

public static void OpenIfClosed(this DbConnection connection)
{
	if (connection!=null && connection.ConnectionString!=String.Empty && connection.State == ConnectionState.Closed)
	{
		connection.Open();
	}
}

Sample VB.NET

<System.Runtime.CompilerServices.Extension> _
Public Shared Sub OpenIfClosed(connection As DbConnection)
	If connection IsNot Nothing AndAlso connection.ConnectionString <> String.Empty AndAlso connection.State = ConnectionState.Closed Then
		connection.Open()
	End If
End Sub

Please rate this snippet

How to list all views in database in MSSQL

To list all views in database in MSSQL you can use the following snippet.

Sample MSSQL

SELECT SCHEMA_NAME(schema_id) AS [schema],name AS [viewname] ,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexed') AS [IsIndexed] ,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexable') AS [IsIndexable], * FROM sys.views;

Please rate this snippet

How to list last backup date of all databases in MSSQL

To list last backup date of all databases in MSSQL you can use the following snippet.

Sample MSSQL

SELECT db.name AS [Databasename], 
case when	MAX(bck.backup_finish_date) is NULL then 
				'No Backup' 
			else 
				convert(varchar(100), MAX(bck.backup_finish_date)) 
end AS [DateOfLastBackup]
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset bck 
	ON db.name = bck.database_name AND bck.type = 'D' WHERE db.database_id NOT IN (2) 
GROUP BY db.name
ORDER BY 2 ASC

Please rate this snippet