Dear Friends

Here you will get all information of your database using this query
try this

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 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
-- last backup
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 'read only' 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


Hope it is helpfull to you.

Count Totals & Sub Totals in Sql SERVER

The SQL Server ROLLUP operator is useful in generating reports that contain subtotals and totals.

Here’s how to use the ROLLUP operator in the Northwind database to give a breakdown of the Orders placed by each Customer as well as the Total number of Orders placed

select ISNULL(intagentid,0) Agent,
count(intagentid) as distributors
from mst_Login where strtype ='Distributor '
group by intagentid with ROLLUP

count & union Example

Recently My friend has some requirement

he want all records from table where status = single and count of records where status = bulk

it is simple in programming , we both do some R&D at last we got the solution

here is the solution

select intagentid,strtype from mst_Login where status = 'single' Union
Select count(*) as intagentid,strType from mst_login where status = 'bulk' group by strType

using this query we get all recrods of single & count of bulk

Using union in sql basic rule is u have to select same number of column in both queries


Thanks
Pradeep Deokar

Use Inbuilt Store Procedure for delete,drop,truncate all tables from one database

Hi Friends ,

Sometime we need to delete all data from all tables from one database ,
so instead of deleting all tables manually , Here is the solution

EXEC sp_MSforeachtable @command1 = "Delete ?"

You can use this Query in multiple ways depending on your requirement

for ex:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

As sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @command1 is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where '?' is the name of the table.

You won't be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

-- First disable referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- Now enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

try this, it will clear your comcepts

EXEC sp_MSforeachtable @command1 = "SELECT * FROM ?" -- Selects all the rows form all the table

EXEC sp_MSforeachtable @command1 = "PRINT '?'" --Just print the tables names with owner(dbo)

It will delete all data from all tables from single database ,
so we will get blank database

Some time programmer test there application by inserting dummy data into tables
after finishing testing it will live on server so before getting live we can remove all dummy data from all table in one query

Hope it is helpfull for u

Thank You
Pradeep Deokar