SELECT*

Executes the specified pass-through query on the specified linked server.This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one. For Eg: SELECT TOP 1* FROM OPENQUERY(TAW,'SELECT Column1,Column2 FROM DatabaseName.dbo.TableName') Before running this query linked server has to becreated in the source server.Here 'TAW' is linkedserver name. And most important thing is if we want to execute the stored procedure which is in other server we need to use OPENQUERY compulsorily.

---To know the permissions on the procedure. SELECT PERM.[Type],PERM.Permission_name FROM SYS.DATABASE_PERMISSIONS as PERM JOIN SYS.OBJECTS AS SO ON PERM.MAJOR_ID=SO.[OBJECT_ID] WHERE SO.[Name] like 'ProcedureName'

Source From : http://www.sqlservercentral.com/articles/Administration/2960/

----In SQL Server 2005 SELECT SERVERPROPERTY('productversion') as Version, SERVERPROPERTY ('productlevel') AS ServicePack, SERVERPROPERTY ('edition') as Edition GO The results are:• The product version (for example, "9.00.1399.06"). • The product level (for example, "RTM"). • The edition (for example, "Enterprise Edition"). GO SELECT @@VERSION GO USE master GO XP_MSVER ---Another way knowing service pack is by build number: Microsoft SQL Server 2005 - 9.00.3042.00 Here '3042' is bulid number.And this is having SP2. Another example is Microsoft SQL Server 2005 - 9.00.3215.00 Here build number is '3215'.This is greater than 3042 build number so this is service pack 2. For Build details we can go see : http://support.microsoft.com/default.aspx?scid=kb;en-q321185

-----Changing the Name of the Server.But it wont Drop the Server. SP_DROPSERVER 'BHI-207\SQLEXPRESS'--Present serverName GO SP_ADDSERVER 'RAMESH MAMILLAPALLI',LOCAL--After running the Query Stop and Restart the Services. GO SELECT @@SERVERNAME

----We can give definitions for tables. EXEC SP_ADDEXTENDEDPROPERTY @name = N'MS_Description', @value = N'This Table is Telling about Emplyee Information.And also Employer', @level0type = N'SCHEMA', @level0name = dbo, @level1type = N'TABLE', @level1name = EmployeeTable; And also we can do the same thing in Table->RightClick->Properties->Extended Properties.

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(...

----To findout how many users are connected to server USE master GO SELECT DISTINCT SYSLOGINS.LoginName,SYSLOGINS.DBName,@@SERVERNAME AS 'ServerName' FROM SYSPROTECTS,SYSLOGINS WHERE SYSADMIN=1

Some times we can get the error as "The log file for database 'DatabaseName' is full. Backup the transaction log for the database to free up some log space". If the Log size is increasing in The Database we can use the Below syntax to reduce. This log size USE DatabaseName BACKUP DatabaseName WITH TRUNCATE_ONLY ---Check SELECT * FROM SYSFILES DBCC SHRINKFILE(LogFileName ) ---To find out the usage of logspace DBCC SQLPERF(LOGSPACE) And also used SP_SPACEUSED '.mdf file' --------------- USE master GO DBCC SQLPERF(LOGSPACE) --To know about the Database LogFile DUMP TRAN DatabaseName WITH NO_LOG-- GO USE DatabaseName SP_HELPFILE GO DBCC SHRINKFILE ('LogFileName') ---The below link will discuss about that minimizing log space http://support.microsoft.com/default.aspx/kb/907511 --Some times  even shrink log is not working. In that time even this is not possible.  use ToPS go alter database ToPS  set recovery simple go ch...

my first blog