Archive for the ‘SQL Server 2005’ Category


To find the nth maximum record, you can try the following:

SELECT o.name,
       user_name(dp.grantee_principal_id) grantee,
       user_name(dp.grantor_principal_id) grantor ,
       dp.permission_name ,
       dp.state_desc,
       o.type_desc
FROM   sys.database_permissions dp 
       INNER JOIN sys.objects   o  ON o.object_id = dp.major_id
                                  AND dp.class     = 1 
                                  AND o.type IN ('U', 'P', 'V', 'Fn') -- U(User Tables), P(Store Procedure), V(View), Fn(Function)
WHERE    dp.type IN ('SL', 'IN', 'UP', 'EX') -- SL(Select), IN(Insert), Up(Update), Ex(Execute)
ORDER BY o.type_desc, 
         o.name;

To find the nth maximum record, you can try the following:

-- Declare variables
DECLARE @lv_string VARCHAR(500);

-- Initialise variable
SET @lv_string = 'Do something      with      the    result  !';

-- Loop until all double spaces are replaced with single space
WHILE CHARINDEX(SPACE(2), @lv_string) > 0 
BEGIN
   SET @lv_string = REPLACE(@lv_string, SPACE(2), SPACE(1));
END;

-- Return string
SELECT @lv_string;

Find the Nth Maximum Record

Posted: November 22, 2011 in SQL Server 2005

To find the nth maximum record, you can try the following:

-- Create table variable
DECLARE @MyTable TABLE 
       (id       INT NOT NULL IDENTITY, 
        order_no VARCHAR(100), 
        amount   INT);

-- Insert test data
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 1', 10);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 2', 20);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 3', 30);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 4', 40);
INSERT INTO @MyTable(order_no, amount) VALUES ('Order 5', 50);

-- Get the 4th maximum record
SELECT * 
FROM  (SELECT *, (SELECT COUNT(t.amount) 
                  FROM  @MyTable t
                  WHERE t.amount >= t2.amount) RANK 
       FROM   @MyTable t2) t3
WHERE  RANK = 4;

To get a list of all tables referenced in a stored procedure, you can try the following:

-- Declare variables
DECLARE @lv_procedure_name VARCHAR(200);

-- Initialise variables
SET @lv_procedure_name = 'YourProcedureName';

SELECT   DISTINCT o.id, 
                  o.name 'procedure_name', 
                  oo.name 'table_name', 
                  d.depid, 
                  d.depnumber 
FROM     sysdepends            d 
         INNER JOIN sysobjects o   ON o.id   = d.id
                                  AND o.name = @lv_procedure_name
         INNER JOIN sysobjects oo  ON oo.id  = d.depid 
WHERE    d.depnumber = 1
ORDER BY o.name,
         oo.name;

Days in Month

Posted: November 21, 2011 in SQL Server 2005

To determine the number of days in a month, you can try the following:

-- Declare variables
DECLARE @ldt_date DATETIME;
DECLARE @li_days  INT;

-- Initialise variables
SET @ldt_date = GETDATE();

-- Calculate days in month
SET @ldt_date = (@ldt_date - (DAY(@ldt_date) - 1));
SET @ldt_date = DATEADD(mm, 1, @ldt_date);
SET @ldt_date = DATEADD(dd, -1, @ldt_date);
SET @li_days = DATEPART(dd, @ldt_date);

-- Return days in month
SELECT @li_days;

Simple Encrypt String Function

Posted: November 21, 2011 in SQL Server 2005

You can use the following function to encrypt a string:

-- Declare variables
DECLARE @lv_pwd VARCHAR(100);

-- Initialise variables
SET @lv_pwd = 'password';

-- Encrypt the string
SELECT PWDENCRYPT(@lv_pwd) encrypt_pwd;

-- Check that the password is correct
SELECT PWDCOMPARE(@lv_pwd, PWDENCRYPT(@lv_pwd), 0) encrypt_pwd_compare;

This will return a 1 for a match and a 0 for a mismatch.


To get the computer network identifcation name, run the following script:

EXEC master..xp_getnetname;