SELECT*

Adds a backup device to an Instance of the Microsoft SQL Server 2005 Database Engine. SP_ADDUMPDEVICE @devtype='disk', @logicalName='AnyUserDefinedName', @PhysicalName='E:\TP0001122.BAK'—Path of BackUp file where it is existing. GO SELECT * FROM SYS.BACKUP_DEVICES

Both Procedures wil give the information about roles(groups) and Users(Members) in the Database. SP_HELPROLE: EXEC SP_HELPROLE @rolename='db_owner' --OR EXEC SP_HELPROLE Gives the information about roles in the Datbase SP_HELPGROUP: EXEC SP_HELPGROUP @grpname='db_owner' --OR EXEC SP_HELPGROUP Gives the Results about which user under which group. SP_HELPROLEMEMBER: EXEC SP_HELPROLEMEMBER @rolename='db_owner' --OR EXEC SP_HELPROLEMEMBER Gives the results members of a role.

We can find Object Dependencies on three ways. Means Which Procedure is Dependent on Which Table, Procedure, Which trigger is dependent on which Table. These things we can findout in three Ways. 1) SP_DEPENDS 2) SYS.SQL_DEPENDENCIES 3) SYS.SYSDEPENDS Go through the below example: CREATE PROCEDURE IndependentProc AS SELECT * FROM SYS.OBJECTS—You can create any table here and give the name. GO CREATE PROC DepedentProc AS EXEC IndependentProc Check the results by using above system catalogue views and procedures SP_DEPENDS 1) SP_DEPENDS IndependentProc 2) SP_DEPENDS DepedentProc SYS.SQL_DEPENDENCIES: SELECT * FROM SYS.SQL_DEPENDENCIES WHERE OBJECT_ID=210099789 –Give Here DepedentProc ID Note:If you have Drop dependent stored procedure.Then the above query does not give you any result. SYS.SYSDEPENDS: SELECT OBJECT_NAME(ID) as DependentObject,OBJECT_NAME(DEPID) as IndependentObject FROM SYS.SYSDEPENDS WHERE ID=210099789 Note:There maybe so many dependent objects.So

------------------sp_addlinkedserver------------------- EXEC sp_addlinkedserver @server='Ramesh', ---This is UserDefined Name @srvproduct='', @provider='SQLNCLI', --That means this is SQL Server @datasrc='S1\instance1'--ServerName or InstanceName and the Want to give username password give these thi server sqlserver user name and password. ------ Some times we need to create linked servers to access .csv files in the system for that we need to create below script. --This linked server will access the files of .csv which are in E drive and in Temp Folder. EXEC SP_ADDLINKEDSERVER RAWFILE, 'Jet 4.0' , 'Microsoft.Jet.OLEDB.4.0' , 'E:\TEMP\' , NULL , 'Text' GO SELECT * FROM RAWFILE...SOSWTFcutdown#csv

ROW_NUMBER is a function which returns the ''sequential number of a row" from the Result set. As well as How many times that particular row has been repeated. CREATE TABLE #Temp (SNO INT,[Name] VARCHAR(25)) ---This value has entered 5 times INSERT INTO #Temp (SNo,[Name]) VALUES(1,'Ramesh') GO -----This value entered has 3 times INSERT INTO #Temp (SNo,[Name]) VALUES(2,'Jagan') GO -----------This value entered has 3 times INSERT INTO #Temp (SNo,[Name]) VALUES(3,'Mohan') go SELECT * FROM #Temp GO SELECT *, ROW_NUMBER()OVER(PARTITION BY SNo ORDER BY [Name]DESC) FROM #Temp ROW_NUMBER function will tell us how many times that particular row has been repeated. If it shows last value as 5.That means that particular row is repeated 4 times. If we want to give sequential number to values we can make small change in the Query. Eg: CREATE TABLE rows ([Name] VARCHAR(20)) GO INSERT INTO rows ([Name]) VALUES('Mahesh') GO INSERT INTO rows ([Name]) VALUES(&#