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'