Tuesday, June 9, 2009

use of cursor to print all Stored procedure of Database

DECLARE @procName varchar(100)

DECLARE RetProcName CURSOR FOR select [Name] from sysobjects where type='P'

OPEN RetProcName

FETCH NEXT FROM RetProcName INTO @procName

WHILE @@FETCH_STATUS = 0

BEGIN

exec sp_helptext @Procname

-- print

Fetch NEXT FROM RetProcName INTO @procName

END

CLOSE RetProcName

DEALLOCATE RetProcName

IDENT_CURRENT and dbcc Checkident

IDENT_CURRENT->
Returns the last identity value generated for a specified table in any session and any scope.
Select ident_current('Table_Name')
-> Checks the current identity value for the specified table and, if needed, corrects the identity value.
By using Following Syntax-
dbcc checkident (Table Name,Reseed,1020000)

How to Refresh Your all the view in single Command in Sql Server

DECLARE @TabName varchar(100)

Declare @T1 varchar(100)

DECLARE Refereshview CURSOR FOR select table_name from information_schema.views

OPEN Refereshview

FETCH NEXT FROM Refereshview INTO @TabName

WHILE @@FETCH_STATUS = 0

BEGIN

set @t1=@TabName

exec sp_refreshview @t1

print @t1

Fetch NEXT FROM Refereshview INTO @TabName

END

CLOSE Refereshview

DEALLOCATE Refereshview

Findout the information about current users and processes of sql server

Syntax-> SP_WHO Login_Name
Or You can Use Sp_Who for all User .

Recreate Index

DECLARE @TabName varchar(100)
Declare @T1 varchar(100)
DECLARE Recreateindex CURSOR FOR select table_name from information_schema.tables OPEN Recreateindex
FETCH NEXT FROM Recreateindex INTO @TabName
WHILE @@FETCH_STATUS = 0
BEGIN
set @t1=@TabName
DBCC DBREINDEX (@T1)
-- print
Fetch NEXT FROM Recreateindex INTO @TabName
END
CLOSE Recreateindex
DEALLOCATE Recreateindex

Find out the information about a specified database or all databases.

Sp_helpdb
->Information about the all databases.
Sp_helpdb Database Name
->Information About the Specified database.

How to find out column information for the specified tables or views

Syntax -> Sp_columns TableName or View Name

Monday, June 8, 2009

How to use Table Variable and advantage of Table variable

Syntax of Table Variable-
DECLARE @TestTable TABLE
(
Pid int,
Pdesc varchar(200)
)
You can use table variables in batches, stored procedures, and user-defined functions (UDFs). We can UPDATE records in our table variable as well as DELETE records.
Update @TestTable Set Pdesc ='PEN' where Pid =1
Select * from @TestTable
Advantage-It provide less locking and logging overhead than table Variable.

How to find Depended Table in view and Procedure

sp_depends View Name /Procedure Name
This Sql Procedure Helps to find out the Dependent Tables and columns in View or Procedure.

Sunday, June 7, 2009

Query for Count Double Records in a Table

Declare @c_no int
Set @c_no=1
select id1,count(id1) as cnt from tab1
group by id1
having count(id1)>@C_No
Note: Result will come this way->
id1 cnt
101 2
103 2

Add Null and string in sql server 2000

Function for adding Null and string :SET CONCAT_NULL_YIELDS_NULL { ON OFF }
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result.

CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself .

SET CONCAT_NULL_YIELDS_NULL OFF

Select ' ' +'Ajay'

Result :Ajay

SET CONCAT_NULL_YIELDS_NULL ON

Select ' ' +'Ajay'
Result :NULL