MS SQL Server: Get Last Bakup Details For All Databases

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10),
DATABASEPROPERTYEX(name, 'status')) AS [Status],
CONVERT(VARCHAR(20), create_date, 103) + ' ' +
CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction log' END + ' ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date)))
+ ' days ago', 'NEVER')) + ' ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' '
+ CONVERT(VARCHAR(20), backup_start_date, 108) + ' '
+ CONVERT(VARCHAR(20), backup_finish_date, 103) + ' '
+ CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name
ORDER BY backup_set_id DESC),'-') AS [Last backup]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME

database_id dbName Status         Creation date
-----------     -------     ----------      ----------------------
1                 testDB    ONLINE     08/04/2003 09:13:36

Last backup
--------------------------------------------------------------------------------------------
Full – 0 days ago – 03/11/2011 15:46:57 – 03/11/2011 15:46:57 (0 seconds)

Keep TSQling ;)

MS SQL Server: Get Complete Information of All Databases

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10),
DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE
DB_NAME(database_id) = DB.name AND
type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files
WHERE DB_NAME(database_id) = DB.name AND
type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE
DB_NAME(database_id) = DB.name AND
type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files
WHERE DB_NAME(database_id) = DB.name AND
type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE Compatibility_level
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' +
CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction log' END + ' ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date)))
+ ' days ago', 'NEVER')) + ' ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' '
+ CONVERT(VARCHAR(20), backup_start_date, 108) + ' '
+ CONVERT(VARCHAR(20), backup_finish_date, 103) + ' '
+ CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name
ORDER BY backup_set_id DESC),'-') AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled'
ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'Autoclose'
ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'Readonly' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'Autoshrink'
ELSE '' END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'Auto create statistics'
ELSE '' END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'Auto update statistics'
ELSE '' END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'Standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'Cleanly shutdown'
ELSE '' END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME

It will genterate the current Sql Server Database(s) resport in reference to each database.

MS SQL Server - Find size of Database using Query

SELECT CASE WHEN (GROUPING(t1.name)=1)
THEN 'All_Tables'
ELSE ISNULL(t1.name, 'unknown')
END [Table Name],
SUM(sys.length) AS [Byte(s)],
SUM(sys.length) / 1024.0 AS [Kilobyte(s)],
(SUM(sys.length) / 1024.0) / 1024.0 AS [Megabyte(s)]
FROM sysobjects t1, syscolumns sys
WHERE t1.xtype = 'u' AND sys.id = t1.id
GROUP BY t1.name
WITH CUBE

Enjoy T-SQL ;)

Find Tables With Foreign Key

SELECT FK.TABLE_NAME, CU.COLUMN_NAME,
PK.TABLE_NAME, PKT.COLUMN_NAME,
C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT t1.TABLE_NAME, t2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
ON t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE t1.CONSTRAINT_TYPE = 'PRIMARY KEY') PKT
ON PKT.TABLE_NAME = PK.TABLE_NAME
-- TO LIMIT LIMIT QUERY RESULT UNCOMMENT
-- PRIMARY KEY
--WHERE PK.TABLE_NAME IN ('TABLE1', 'TABLE2')
-- FORGIGN KEY
--WHERE FK.TABLE_NAME IN ('one_thing', 'another')