I’m sure there are other ways to do this but this is pretty easy:
DECLARE
@SPID INT,
@Handle binary(20),
@retVal varchar(8000)
SET
@SPID = 148
SELECT
@Handle=sql_handle
FROM
master..sysprocesses
WHERE spid = @SPID
SELECT
SqlCommand=convert(varchar(8000), [text])
FROM
::fn_get_sql(@Handle)
References
Never use the sp_ prefix for a user-stored procedure. One reason is that it can make it hard to tell which sprocs are delivered and which are user-defined. Another is that if you prefix your sprocs with sp_, SQL searches for the prefix in the master db each time the procedure is called, before looking in the local database. Use usp_ instead.
From Microsoft eLearning Course 3595: Establishing Database Conventions and Standards for Microsoft® SQL Server™ 2005.
This query works on SQL Server 2000 and above:
USE [master]
SELECT
CAST(SERVERPROPERTY(’productversion’) AS VARCHAR) + ‘ - ‘
+ CAST(SERVERPROPERTY(’productlevel’) AS VARCHAR) + ‘ (’
+ CAST(SERVERPROPERTY(’edition’) AS VARCHAR) + ‘)’
You see the word GO in a lot of T-SQL scripts but do you actually know what it is or how to use it?
GO is actually not a part of T-SQL and is never passed to SQL Server. It is used by clients such as osql, sqlcmd, SSMS and Query Analyzer to control how commands are batched and sent to the server.
Read more about the GO keyword.
When you add a new LUN to a SQL Server cluster, you start by adding it as a partition under Windows Disk Management. However, SQL Server will not recognize the partition at this point. You must also add the disk resource to the dependency list for the SQL Server resource in the cluster.
DATETIME requires 8 bytes of storage and stores dates from 1/1/1753 to 12/31/9999. It is accurate to about 3 milliseconds.
SMALLDATETIME requires only 4 bytes of storage. It can stores dates from 1/1/1900 to 6/6/2079 and is accurate to 1 minute.
If you’re not in the mood to wade through BOL, you can find a quick overview with a fair amount of detail on SQL Server datatypes here.
The EXISTS and NOT EXISTS operators can often use an index whereas the LIKE and NOT LIKE operators cause a table scan. This makes EXISTS and NOT EXISTS tend to be more efficient.
Transaction log files should be optimized for sequential IO and secured against a single point of failure. Accordingly, RAID1 (mirroring) or RAID 10 (striping across mirrors) is recommended for transaction logs.