Some Important SQL Queries

Mani Bhushan

Rate me:

4.64/5 (14 votes)

6 Nov 2011CPOL

38.7K

Some Important SQL Queries

List all the databases on SQL Servers.

You can do it by following System Stored Procedures and queries:

SQL

EXEC sp_databases

EXEC sp_helpdb

SELECT name
FROM sys.databases

SELECT name
FROM sys.sysdatabases

List all Tables in the database on SQL Servers.

You can find out all tables within a database by the following query:

SQL
```
SELECT *
FROM sys.Tables

SELECT * FROM information_schema.tables

SELECT * FROM sysobjects WHERE xtype='U'
```
Here is a list of other object types you can search for as well:

C: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure
List all Stored Procedures in the database on SQL Servers.

You can find out all Stored Procedures within a database by the following query:

SQL
```
SELECT name
FROM sys.objects
WHERE type = 'P'
If you want to find it within a period then;

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) <5
```
The above query will find out all Stored Procedures created within 5 days.
List all User Defined Functions in the database on SQL Servers.

You can find out all User Defined Functions within a database by the following query:

SQL
```
SELECT *
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
```

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Mani Bhushan

Software Developer Max Bupa Health Insurance Company Ltd.

India

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

1-------

SELECT pm.PatientID , pm.Patient_Name , CONVERT(varchar(10),DateOfRegistration,120) as P_RegistDate,
DATENAME(MM,DateOfRegistration) as Month,
fd.DoctorId, fd.TotalAmount
FROM PATIENTMASTER pm JOIN Fees_Details_for_Out_Patients fd ON pm.PatientId = fd.PatientId
WHERE fd.TotalAmount = (SELECT MAX(fd.TotalAmount) FROM Fees_Details_for_Out_Patients fd JOIN PATIENTMASTER pm ON DATEPART(Month,pm.DateOfRegistration) = '11' )

----- 2-------

select FeesID as "Fees ID", DoctorAddress1 as "First Address"
FROM Doctor_Fees_Master join
DoctorMaster on Doctor_Fees_Master.DoctorId = DoctorMaster.DoctorId
where DoctorAddress1 like '%pune%' order by FeesID desc
FOR XML AUTO

END

--- 3-------

SELECT dm.DoctorID , dm.DoctorName , dm.Gender , SUM(fdin.TotalAmount) AS SUM_IN_PATIENT,
SUM(fdout.TotalAmount) AS SUM_OUT_PATIENT,
(fdin.TotalAmount + fdout.TotalAmount ) AS TOTAL_AMOUNT
FROM dbo.DOCTORMASTER dm JOIN
Fees_Details_for_In_Patients fdin
ON dm.DoctorId = fdin.DoctorID JOIN
Fees_Details_for_Out_Patients fdout ON fdout.DoctorID = dm.DoctorID
WHERE (fdin.TotalAmount + fdout.TotalAmount ) = (SELECT (Min(fdin.TotalAmount + fdout.TotalAmount ))
FROM Fees_Details_for_In_Patients fdin JOIN
Fees_Details_for_Out_Patients fdout ON fdin.DoctorId = fdout.DoctorId )
GROUP BY dm.DoctorID,dm.DoctorName,dm.Gender,fdin.TotalAmount,fdout.TotalAmount

---- 4-------

SELECT rm.RoomId,rm.RoomDescription, rm.RoomType,rm.Number_Of_Beds,rrtm.Rent_Per_Day
FROM ROOMMASTER rm JOIN Room_Rate_Master rrtm ON rrtm.RoomId = rm.RoomId
WHERE rm.Number_Of_Beds = (SELECT MAX(rm.Number_Of_Beds) FROM RoomMaster rm) ORDER BY rm.RoomId desc

------ 5-------

SELECT dm.DoctorId , dm.DoctorName , fdin.DoctorsFees
FROM DOCTORMASTER dm JOIN Fees_Details_for_In_Patients fdin ON
dm.DoctorId = fdin.DoctorId JOIN DOCTOR_FEES_MASTER dfm ON dfm.DoctorId = fdin.DoctorId WHERE
fdin.DoctorsFees > dfm.Fees_for_In_Patients GROUP BY dm.DoctorId,dm.DoctorName,fdin.DoctorsFees

----- 6-------

SELECT ROW_NUMBER() OVER (
Partition by gender ORDER BY DateOfBirth) AS RowNumber,
DoctorId, DoctorName, DoctorAddress1,Gender
FROM dbo.DoctorMaster

------7-------

select RANK() OVER ( partition by roomtype order by rent_per_day) as rankcl , Roomdescription
from dbo.Room_Rate_Master a inner join dbo.RoomMaster b on a.RoomId=b.RoomId

----- 8-------

With T(Patient_Name, Patient_Address1, Phone,Sex, DateOfBirth)
AS
(
SELECT a.Patient_Name,Patient_Address1, A.Phone,A.Sex,A.DateOfBirth From dbo.PatientMaster A
Inner join dbo.PatientDetails b on A.PatientId = b.PatientId
)

SELECT * FROM T
WHERE T.DateOfBirth > 1966-05-6
ORDER BY T.Sex

------ 9-------

CREATE TRIGGER TGR_ON_DoctorMaster on DoctorMaster
FOR INSERT
AS DECLARE @DoctorId int, @DoctorName varchar(30),@Audit_Action varchar(100);
SELECT @DoctorId = i.DoctorID FROm inserted i;
SELECT @DoctorName = i.DoctorName FROm inserted i;
SET @Audit_Action = 'Inserted Record -- After Insert Trigger';
INSERT INTO dbo.AUDIT (DoctorID, DoctorName, Audit_Action, Audit_Timestamp)
values (@DoctorId ,@DoctorName, @Audit_Action,getdate());

---- 10-------

SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM InvoiceAmount
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)
)AS piviot

------ 11-------

SELECT [PatientId]
,[Patient_Name]
,[DateOfBirth]
,EOMONTH([DateOfBirth]) as [EOMDateOfBirth]
,IIF([Blood_Group] = 'O+', 'Required', 'Not Required') AS Result
,[Phone]
,[DateOfRegistration]
FROM [CASQLENB].[dbo].[PatientMaster]

----- 12-------

SELECT [RoomId]
,[PatientId]
,[DoctorId]
,[Room_Rent_Per_day]
,LAST_VALUE([Room_Rent_Per_day]) OVER(ORDER BY [PatientId]
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS LstValue
FROM [CASQLENB].[dbo].[Fees_Details_for_In_Patients]

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.