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')

Speed up by Disabling N/W waiting...

Speed up system start up.

1. Open notepad
2. Copy below code in notepad:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT\CurrentVersion\Winlogon]
"SyncForegroundPolicy"=dword:00000000

3. Save file as DisableNW.Reg anywhere on your system
4. Double click on it to run it
5. Logoff or restart your system [optional]

In normal case no one needs the default setting that cause the user to wait for network status before start working on Windows Operation System. If you disable this setting, then this will increase the startup speed. No side effect. njy...

Enable Network waiting...

This is deafult setting by Windiows. But, I am putting this here in case you want to know how to enable this setting if its disabled by my next post ;)

1. Open notepad
2. Copy below code in notepad:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT\CurrentVersion\Winlogon]
"SyncForegroundPolicy"=dword:00000001

3. Save file as EnableNW.Reg anywhere on your system
4. Double click on it to run it
5. Logoff or restart your system [optional]

njy...

Autorun Virus Protection

This script will provide your system little more protection. After running the following script on your system, virus from any portable device can't get automatic access to your system. So, it saves you system from unknown and new dangerous viruses with automatic running feature.

1. Open notepad
2. Copy below code in notepad:

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer]
"NoDriveTypeAutoRun"=dword:000000ff
[HKEY_USERS\.DEFAULT\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer]
"NoDriveTypeAutoRun"=dword:000000ff

3. Save file as NoAuto.Reg anywhere on your system
4. Double click on it to run it
5. Logoff or restart your system [optional]

Now, no virus get automatic activation on your system njy