DeptNo,
sal,
DENSE_RANK() OVER(PARTITION BY DeptNo ORDER BY sal DESC) AS dns_rnk
FROM emp;
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)
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)
Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...