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

User Defined Functions (UDF) Limitations

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

• UDF has No Access to Structural and Permanent Tables.
  • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
• UDF Accepts Lesser Numbers of Input Parameters.
  • UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
• UDF Prohibit Usage of Non-Deterministic Built-in Functions
  • Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
• UDF Returns Only One Result Set or Output Parameter
  • Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
• UDF can not Call Stored Procedure
  • Only access to Extended Stored Procedure.
• UDF can not Execute Dynamic SQL or Temporary Tables
  • UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
• UDF can not Return XML
  • FOR XML is not allowed in UDF
• UDF does not support SET options
  • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
• UDF does not Support Error Handling
  • RAISEERROR or @@ERROR are not allowed in UDFs.

As I said earlier this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

convert varchar to int in sql

Few of the questions I receive very frequently. I have collect them search it & here is some important one's

How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table

Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.

Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.

try catch with RAISEERROR function

here we can learn how to use try catch block in sql server

as a programmer i never use try catch block in any of my query or store procedure,

but recently i got stuck in one store procedure, My SP not executed & i didnt get exact error message also,

after searching i got solution that using try catch block with RAISEERROR finction i can catch the exact error, so using this i got exact error, & i solved that within a second

so it is a good practice to use try catch in sql.

RAISEERROR function gives us exact error definition.

Example 1 : Simple TRY…CATCH without RAISEERROR function
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT 'Divide by zero error encountered.' ErrorMessage
END CATCH;
GO

ResultSet:
ErrorMessage
———————————
Divide by zero error encountered.

Example 2 : Simple TRY…CATCH with RAISEERROR function
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorMessage = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, 16, 1);
END CATCH;
GO

ResultSet:
Msg 50000, Level 16, State 1, Line 9
Divide by zero error encountered.

datetime conversion Solution

Hello Friends ,

recently me & my friend vinay had some issue in datetime datatype, in mis report we cant find records of same date ..... means we want records of todays date i.e 09/01/2010 and 09/01/2010

we was using simple query like

select * from table_name where columndate between '09/01/2010' and '09/01/2010'

using this query we unable to get data still there is data present in the table for the same date

then we do some R&D & we got the solution that

we have to convert our columndate to the same format as the input.


in our table date stored as a yyyy/MM/dd format & i am passing query in MM/dd/yyyy format
so for that
we change our query structure like this

select * from table_name where convert(varchar,columndate,101) between '09/01/2010' and '09/01/2010'

then we got exact records........ at last we got success............

here is the table where u will get all possible date format as you want

DATE FORMATS
Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30

TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

Back up all database in single query

Hi guys, for programmer , taking backup using GUI is robust work, suppose you are working on more than 3 database then taking backup of 3 databases using GUI is time wasting job,

here is the solution you can take backup of all databases in one shot try this

you can set path as you wish

ex: @path = 'D:\dbBackup\' you have to create folder before executing query



DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\dbBackup\' -- your folder name instead of dbbackup

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

--

Generate Random Number in Sql

Here You can generate Random number using lower number and upper number


DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

Enable , Disable Sa Login

In SQL SERVER 2005, all the login (including ‘sa’ ) can be enabled and disabled using ALTER LOGIN command.

To disable ‘sa’ login:
ALTER LOGIN sa DISABLE
GO

To enable ‘sa’ login:
ALTER LOGIN sa ENABLE
GO

Also for additional security (prevent educated guess from hackers) the name of the ‘sa’ account can be changed.
ALTER LOGIN [sa] WITH NAME = [AdminUser]
GO

Sql Logical Query processing phases

Hi all,

Here i am starting SQL Tour for us where you and me both will learn all required fundamental regarding SQL
so come with me for this beautiful tour

I am starting this tour with not typical t-sql or sql basics
i want to share with you how sql works ,how your query execute logically

So Lets start with Logical query processing phase

lets take a example

(8) select (9) distinct (11) ( top_specification .. select_list)
(1) from (left_table)
(3) (join_type) join (right_table)
(2) on (join_condition)
(4) where (where_condition)
(5) group by (group_by_list)
(6) with {cube | rollup}
(7) having (having_condition)
(10) order by(order_by_list)

don't be panic , i will explain you all this. you must be thinking why i put numbers?

First aspect of SQL that is different from other programming language is the order in which code is processed , In most programming languages code is processed in order in which it is written.

So lets start with How Sql process particular query

Each step generates a virtual table that is used as a input to the following step. this virtual tables
are not seen to caller, only table generated by the final step returned to the caller

in above example code is executed like this

1 . From : if join is present there then (cross join) join is performed between the two tables in the from clause and as a result virtual table VT1 is generated ..
ex: (from mstcustomer MC join mstdepartment MD)

2. On : the on filter is applied to VT1. only rows from which the is true are inserted to VT2 (virtual table 2)

3.Outer(join) : if an outer join is specified rows from the preserved table or tables for which match was not found are added to the rows from VT2 as outer rows, generating VT3

4.Where: where filter is apllied to VT3 . only rows from which(where_condition) is true are inserted to VT4

5.Group By : the rows from VT4 are arranged in a group based on the column list specified in group by clause . VT5 is generated

6.Cube | Roll up :supergroups are added to the rows from VT5, generating VT6

7.Having :having clause is applied to VT6 . only groups for which the is true is inserted to VT7

8.Select : the select list is processed , generating VT8

9.Distinct: duplicate rows are removed from VT8. VT9 is generated.

10.order by : the rows from VT9 are sorted according to the column list specified in the order by clause. A cursor is generated VT10

11.TOP: the specified numbers or percentage of rows is selected from the beginning of VC10. table VT11 is generated & return to caller ( that is your output)