SQL Server - List-out Procedures, Functions, Triggers

In order to know which procedures, functions, and triggers exist in the database, the following queries can help you to find this out.:

The below set of statements can be used to retrieve stored procedures and the number of parameters in it:

Use [Test1]
Go

SELECT SPECIFIC_NAME, COUNT(PARAMETER_NAME)
FROM INFORMATION_SCHEMA.PARAMETERS
GROUP BY SPECIFIC_NAME

The below statements are an alternative to the statements above and can be used for the same purpose:

Use [Test1]
Go

SELECT SPECIFIC_NAME, COUNT(PARAMETER_NAME),
CASE WHEN xtype = 'P' THEN 'Stored Procedure'
WHEN xtype IN ('FN', 'IF', 'TF') THEN 'Function'
END AS ProcType
FROM INFORMATION_SCHEMA.PARAMETERS a
INNER JOIN sysobjects b ON b.name = a.SPECIFIC_NAME
GROUP BY SPECIFIC_NAME, xtype

To retrieve data about a specific procedure, either the following statement:

SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME=3D'MentionProcName'

Or the below statement can be used to list out the procedure details:

EXEC sp_stored_procedures

To know the parameters set for the desired procedures, you can use the below SELECT statement:

SELECT a.name, a.type_desc, b.name, parameter_id FROM sys.procedures a
INNER JOIN sys.parameters b ON a.object_ID = b.object_ID
ORDER BY a.name, parameter_id

Note that Parameter_ID in the above query refers to a serial order of the parameters that belong to the procedures.

The following statements can help you to retrieve the stored procedures or functions along with the number of lines in the code:

SELECT so.name AS ProcedureName,
CONVERT(INT, (LEN(sc.text) - LEN(REPLACE(sc.text, char(10), ''))))-1 as CodeLines,
CASE WHEN so.xtype = 'P' THEN 'Stored Procedure'
WHEN so.xtype IN ('FN', 'IF', 'TF') THEN 'Function'
END AS ObjectType
FROM sysobjects so
INNER JOIN syscomments sc ON sc.id = so.id
WHERE so.xtype IN ('P', 'FN', 'IF', 'TF') AND so.category = 0

To the above statement, you can include the following clause to avoid system procedures / functions.
AND so.name NOT IN
('fn_diagramobjects',
'sp_alterdiagram',
'sp_creatediagram',
'sp_dropdiagram',
'sp_helpdiagramdefinition',
'sp_helpdiagrams',
'sp_renamediagram',
'sp_upgraddiagrams',
'sysdiagrams')

And the following query can help you to list the triggers that are created in the database:

SELECT OBJECT_NAME(ID), OBJECT_NAME(deltrig),
OBJECT_NAME(instrig), OBJECT_NAME(updtrig)
FROM sysobjects
WHERE xtype ='U' AND (deltrig > 0 OR instrig > 0 OR updtrig > 0)

SQL Server - Insert Data From Linked Server Table

In a situation where Servers are linked, it is possible to retrieve data from a table in one server to a table on the other server.
Following is the method to carry this out:

SELECT @@SERVERNAME

This will return the server’s name to which you are currently connected to.

SELECT * FROM [bngstagedb]. testDB.dbo.temp2 WITH (NoLock)

In the above statement ‘WITH’ clause is significant. If not used then you will receive the error 'Remote table-valued function calls are not allowed.'. Hence, it is required for you to either remove NoLock clause in the statement or add 'WITH' clause to the NoLock.

Below statement will allow you to make a copy of the required table in the current server from the linked server:

SELECT * INTO temp2
FROM [bngstagedb].testDB.dbo.temp2 WITH (NoLock)

And the below statement will allow you to insert values in an existing table from the linked server:

INSERT INTO temp2
SELECT * FROM [bngstagedb].testDB.dbo.temp2 WITH (NoLock)

SQL Server - Self Joins

To join tables, at-least two tables are required. It does not necessarily have to be two different tables :). It is possible to join a table with itself to retrieve the required data.

When a table is joined with itself then such type of join is called Self Join. This is useful when you want to retrieve the data in the form of organizational structure. The same table is used twice using aliases in self join.

The methodology involves joining the records in a table with other records in the same table.

Following is an example of a Self Join.

CREATE TABLE #sample1 (EmpID INT, EmpName VARCHAR (30), ManageriD INT)

INSERT INTO #sample1 VALUES (1001, 'Reman D Joseph', Null)
INSERT INTO #sample1 VALUES (1002, 'Samuel Peter', 1001)
INSERT INTO #sample1 VALUES (1003, 'Arihanth', 1005)
INSERT INTO #sample1 VALUES (1004, 'Ayesha Khan', 1001)
INSERT INTO #sample1 VALUES (1005, 'Kirlosker Joseph', 1002)
INSERT INTO #sample1 VALUES (1006, 'Firdouse', 1004)
INSERT INTO #sample1 VALUES (1007, 'Peter DeSouja', 1002)

SELECT * FROM #Sample1

Observe the following by looking into the values:

Samuel Peter's Employee ID is 1002 and his manager's employee ID is 1001. The ID 1001 is Reman D Joseph's employee ID hence Samuel Peter's manager is Reman D Joseph, right?

Let's now check how this information can be more conveniently retrieved using SQL Joins:

SELECT DISTINCT s1.EmpName, s2.EmpName FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD

The above query will return all the employees with a manager along with their names.

What if we want to retrieve a list of all the employees regardless of having a manager or not?

SELECT DISTINCT s1.EmpID, s1.EmpName, s2.EmpName FROM #sample1 s1
LEFT JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD
ORDER BY s1.EmpID

If you want to combine the employee’s name with his manager's name, then use the following query:

SELECT DISTINCT s1.EmpName + '''s Manager is ' + s2.EmpName
FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD

To generate a list of employees who are not managers use the following query (An example for Sub-query and Joins)

SELECT EmpID, EmpName, ManagerID FROM #Sample1
WHERE EmpID NOT IN(
SELECT DISTINCT s1.ManageriD FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD)

To generate a list of Only managers, use the query below:

SELECT EmpID, EmpName, ManagerID FROM #Sample1
WHERE EmpID IN(
SELECT DISTINCT s1.ManageriD FROM #sample1 s1
JOIN #sample1 s2 ON s2.EmpID = s1.ManageriD)

DROP TABLE #sample1

SQL Server - Date Time Functions

Here are a few examples of functions that can help you manipulate DATE in different manners. These will comprise most of your daily job routines. The below set of Date/Time functions are used consistently by SQL Users as they are most oftenly required to retrieve specific data from the database.

---------------------------------------------------------------------------------
Please note that this article is created on 3rd of Febraury 2010 and getDate() function will return current date.
---------------------------------------------------------------------------------

The Following statement will return the date, month and year.
SELECT DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))
Result : 2010-02-03 00:00:00.000

SELECT LEFT(DATEADD(Day,0, DATEDIFF(Day, 0, GETDATE())), 12)
Result : Feb 3 2010

The following statement will return only date.
SELECT DATEPART(Day, GETDATE())
Result : 3

The Following statement will return the day-number for the week.
SELECT DATEPART(dw, GETDATE())
Result : 4

The Following statement will return day name of the week.
SELECT DATENAME(dw, GETDATE())
Result : Wednesday

The Following statement will return month of the year.
SELECT DATENAME(mm, GETDATE())
Result : February

The Following statements will return the year. You can use 'yy' instead of 'year'
SELECT DATENAME(year, GETDATE())
SELECT DATEPART(yy, GETDATE())
Result : 2010

The Following statements will return the month-number for the year. You can use 'mm' instead of 'month'.
SELECT DATEPART(month, GETDATE())
Result : 2

Difference between two dates (number of days between two dates). You can use 'd' or 'dd' instead of 'Day'
SELECT DATEDIFF(Day, '2010-01-01', GETDATE())
Result : 33

Difference between two dates (number of months between two dates)
SELECT DATEDIFF(Month, '2010-01-01', GETDATE())
Result : 1

Difference between two dates (number of minutes between two dates)
SELECT DATEDIFF(Minute, '2010-01-01', GETDATE())
Result : 48531
SELECT CONVERT(DECIMAL(5,2), DATEDIFF(Second, '2010-02-01 10:55:00.000', '2010-02-01 19:58:10.498')/60.00)
Result : 543.17

Difference between two dates (number of seconds between two dates)
SELECT DATEDIFF(Second, '2010-01-01', GETDATE())
Result : 2911914

Difference between two dates (number of years between two dates)
SELECT DATEDIFF(Year, '2010-01-01', GETDATE())
Result : 0

SELECT Day2-Day1 FROM (
SELECT
Day1=CONVERT(DATETIME, '2010-02-01 05:47:53.497'),
Day2=CONVERT(DATETIME, '2010-02-01 06:47:10.420')) Date
Result : 1900-01-01 00:59:16.923

SELECT
CONVERT(VARCHAR(10),
CASE WHEN(
DATEDIFF(hh, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497')) >= 24)
THEN
DATEDIFF(hh, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497'))-24
ELSE
DATEDIFF(hh, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497'))
END)
+
SUBSTRING(CONVERT(VARCHAR(30),
DATEADD(mi, DATEDIFF(Minute, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497')),
DATEADD(s, DATEDIFF(Second, CONVERT(DATETIME, '2010-02-01 01:00:53.497'),
CONVERT(DATETIME, '2010-02-02 23:59:59.497')), 0)), 109), 15, 6) as 'HH:MM:SS'
Result : 22:58:06
-----------------------------------------------------------------
DATE Format using CONVERT Function
-----------------------------------------------------------------
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Result : 'Feb 1 2010 12:11PM'
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)
Result : '02/01/2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 102)
Result : '2010.02.01'
SELECT CONVERT(VARCHAR(20), GETDATE(), 103)
Result : '01/02/2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 104)
Result : '01.02.2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 105)
Result : '01-02-2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 106)
Result : '01 Feb 2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 107)
Result : 'Feb 01, 2010'
SELECT CONVERT(VARCHAR(20), GETDATE(), 108)
Result : '12:15:48'
SELECT CONVERT(VARCHAR(40), GETDATE(), 109)
Result : 'Feb 1 2010 12:16:32:570PM'
SELECT CONVERT(VARCHAR(40), GETDATE(), 110)
Result : '02-01-2010'
SELECT CONVERT(VARCHAR(40), GETDATE(), 111)
Result : '2010/02/01'
SELECT CONVERT(VARCHAR(40), GETDATE(), 112)
Result : '20100201'
SELECT CONVERT(VARCHAR(40), GETDATE(), 113)
Result : '01 Feb 2010 12:17:26:710'
SELECT CONVERT(VARCHAR(40), GETDATE(), 114)
Result : '12:17:40:040'
SELECT CONVERT(VARCHAR(40), GETDATE(), 120)
Result : '2010-02-01 12:18:24'
SELECT CONVERT(VARCHAR(40), GETDATE(), 121)
Result : '2010-02-01 12:18:37.007'
SELECT CONVERT(VARCHAR(40), GETDATE(), 126)
Result : '2010-02-01T12:19:07.160'
SELECT CONVERT(VARCHAR(40), GETDATE(), 127)
Result : '2010-02-01T12:19:34.537'
SELECT CONVERT(VARCHAR(40), GETDATE(), 130)
Result : '17 ??? 1431 12:20:37:580PM'
SELECT CONVERT(VARCHAR(40), GETDATE(), 131)
Result : '17/02/1431 12:21:04:597PM'

SQL Server - CHARINDEX - Oracle INSTR

There is a ‘CharIndex’ function in SQL Server which is similar to the Oracle ‘Instr’ function.

In Oracle, the syntax of the INSTR function is :

instr( string1, string2 [, start_position [, nth_appearance ] ] )

String1 in the above syntax represents the string that you need to search in, whereas String2 is the substring that needs to be search for within the String1.

start_position and nth_appearance are optionals. Through start_position you can specify from which point in string1 the search should start and nth_appearance is to specify the number of occurance. By default start_position and nth_appearance is set to 1.

Now, let's check with one example.

SELECT INSTR('Rhythm of the band', 'th') FROM Dual;
SELECT INSTR('Rhythm of the band', 'th', 1, 1) FROM Dual;

The above select statements would return 4 as your output since 'th' was found to be positioned at the 4th character. But 'th' has been spelled twice in "Rhythm of the band" and if you require a second occurance from the string, then you need to change the statement like this_

SELECT INSTR('Rhythm of the band', 'th', 1, 2) FROM Dual;

The above statement however would return 11 as your output since 'th' was found at 11th position in string1.

Now, let's work on SQL Server for the same.

CHARINDEX is the alternative in SQL Server for INSTR function but it is not exactly its equivalent.

CHARINDEX ( expression1 , expression2 [ , start_location ] )

The above syntax is the same as with INSTR function except for nth_appearance. We donot have an option to set the nth_appearance.

SELECT CHARINDEX('th','Rhythm of the band')

The above statement would return 4 as your output since 'th' was found at the 4th character position.

SELECT CHARINDEX('th','Rhythm of the band', 5)
SELECT CHARINDEX('th','Rhythm of the band', 6)
SELECT CHARINDEX('th','Rhythm of the band', 7)
SELECT CHARINDEX('th','Rhythm of the band', 8)

The above statements would return 11 as your output since 'th' was found at the 11th position after the first 5 characters.

If you require an exact behaviour of INSTR function in SQL Server, then follow the link to create a user defined function.

http://www.dbforums.com/microsoft-sql-server/1101462-equivalent-oracles-instr-4-parameters-sql-server.html

SQL Server - Sample Database Link

Here is the link for Microsoft SQL Server Community Projects & Samples. They have provided the best examples and databases in the following link.

Codeplex SQLServerSamples

SQL Server - List Out Number of Rows in Database

To List-Out The Number Of Rows In Each Table From The Current Database.
To Find-Out Number Of Rows In Each Table In Database.

/****************/
--METHOD-1
/****************/
Using A Procedure_
DECLARE
@TotalRows INT,
@FirstID INT,
@table VARCHAR(255),
@cmd VARCHAR(500)

CREATE TABLE #tableData (TableName VARCHAR(255), TotalRows INT)
CREATE TABLE #temp1 (col1 INT IDENTITY, TableName VARCHAR(255))

INSERT INTO #temp1
SELECT table_name FROM Information_Schema.Tables
WHERE table_type = 'base table'

SET @TotalRows = @@ROWCOUNT
SET @FirstID = 1

WHILE @FirstID <= @TotalRows BEGIN

SELECT @table = TableName FROM #temp1 WHERE col1 = @FirstIDSET @cmd = 'SELECT ''' + @table + ''', count(*) FROM ' + @tableINSERT INTO #tableData EXEC (@cmd)SET @FirstID = @FirstID + 1END

SELECT * FROM #tableData ORDER BY TotalRows DESC

DROP TABLE #tableData
DROP TABLE #temp1

CREATE TABLE #tableInfo
(
TableName VARCHAR(255),
TotalRows INT
)

INSERT #tableInfo
EXEC sp_msFOREachTable
'SELECT PARSENAME(''?'', 1),
COUNT(*) FROM ?'

SELECT * FROM #tableInfo ORDER BY TotalRows DESC

DROP TABLE #tableInfo
/****************/
--METHOD-4
/****************/
CREATE TABLE #tableInfo
(
TableName VARCHAR(255),
TotalRows INT
)

EXEC sp_msFOREachTable
'INSERT INTO #tableInfo
SELECT ''?'', COUNT(*) FROM ?'

SELECT * FROM #tableInfo ORDER BY TotalRows DESC

DROP TABLE #tableInfo

/****************/
--METHOD-2
/****************/
Using A Cursor_
DECLARE @table VARCHAR(255),
@cmd VARCHAR(500)

CREATE TABLE #tableData (TableName VARCHAR(255), TotalRows INT)

DECLARE cursor_tableData CURSOR FOR
SELECT table_name FROM Information_Schema.Tables
WHERE table_type = 'base table'

OPEN cursor_tableData

FETCH NEXT FROM cursor_tableData INTO @table
WHILE @@fetch_status = 0
BEGIN
SET @cmd = 'SELECT ''' + @table + ''', COUNT(*) FROM ' + @table
INSERT INTO #tableData EXEC (@cmd)
FETCH NEXT FROM cursor_tableData INTO @table
END
CLOSE cursor_tableData
DEALLOCATE cursor_tableData

SELECT * FROM #tableData ORDER BY TotalRows DESC
DROP TABLE #tableData


The following methods are using un-documented stored procedure 'sp_msFOREachTable'. There is a cursor declared in that which loops through each table in the current database and executes a script that you define.

/****************/
--METHOD-3
/****************/

SQL Server - Monthly Report - Group by Month

The following example guide you to retrieve a monthly report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a monthly report by GROUPING DATE column.

CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Test1 VALUES(221001, 'A1020101', 2000.00, '2010-01-20 13:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020102', 3300.00, '2010-01-20 13:33:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020103', 1100.00, '2010-01-20 15:01:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020104', 5400.00, '2010-01-20 15:49:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020105', 5200.00, '2010-01-20 16:30:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020106', 3400.00, '2010-01-20 16:59:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020107', 1200.00, '2010-01-20 17:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020108', 4200.00, '2010-01-20 18:22:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020109', 2100.00, '2010-01-20 18:43:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020110', 3400.00, '2010-01-20 19:12:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020111', 5500.00, '2010-01-20 22:19:00:000')

SELECT * FROM Test1

SELECT
DATENAME(mm, PaidDate) Month,
YEAR(PaidDate) Year,
COUNT(*) iCount,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
DATENAME(mm, PaidDate),
Year(PaidDate),
Month(PaidDate)
ORDER BY Month(PaidDate)

SQL Server - Daily Report - Group by Date

Like hourly report, the following example guide you to retrieve a daily report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a daily report by GROUPING DATE column.
CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Test1 VALUES(221001, 'A1020101', 2000.00, '2010-01-20 13:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020102', 3300.00, '2010-01-20 13:33:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020103', 1100.00, '2010-01-20 15:01:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020104', 5400.00, '2010-01-20 15:49:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020105', 5200.00, '2010-01-20 16:30:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020106', 3400.00, '2010-01-20 16:59:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020107', 1200.00, '2010-01-20 17:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020108', 4200.00, '2010-01-20 18:22:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020109', 2100.00, '2010-01-20 18:43:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020110', 3400.00, '2010-01-20 19:12:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020111', 5500.00, '2010-01-20 22:19:00:000')

SELECT * FROM Test1

SELECT
CONVERT(VARCHAR(10), PaidDate, 101) Day,
COUNT(*) TransactionsCount,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
CONVERT(VARCHAR(10), PaidDate, 101)

SQL Server - Hourly Report - Group by Hour

Sometimes it requires to know how many inserts are happend every hour in a transactions table OR how many payments made every hour OR to retrieve the hourly based report, whatever term it is, the requirement is to generate a report based on hours from a datetime column. If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Follow the examples.
CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Test1 VALUES(221001, 'A1020101', 2000.00, '2010-01-20 13:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020102', 3300.00, '2010-01-20 13:33:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020103', 1100.00, '2010-01-20 15:01:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020104', 5400.00, '2010-01-20 15:49:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020105', 5200.00, '2010-01-20 16:30:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020106', 3400.00, '2010-01-20 16:59:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020107', 1200.00, '2010-01-20 17:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020108', 4200.00, '2010-01-20 18:22:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020109', 2100.00, '2010-01-20 18:43:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020110', 3400.00, '2010-01-20 19:12:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020111', 5500.00, '2010-01-20 22:19:00:000')

SELECT * FROM Test1

SELECT
CONVERT(VARCHAR(10), PaidDate, 101) Day,
CONVERT(VARCHAR(2), PaidDate, 108) Hour,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
CONVERT(VARCHAR(10), PaidDate, 101),
CONVERT(VARCHAR(2), PaidDate, 108)