SQL query to find the size of a database

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Sample'
GO

Here Sample is the name of the database.If you remove the WHERE condition you will find the result for all the databases.

SQL Query to find Last Modified date ,Created date of a stored Procedure

This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.
USE GSIt;
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'USP_JOIN_COLLEGE_DETAILS'
GO

Here GSIt is the name of the database and USP_ COLLEGE_DETAILS is the name of the stored procedure.

Rollback,Commit,Truncate Example

/* CREATE TABLE */
CREATE TABLE TRUNTEST(ID INT)
/* INSERT TABLE VALUES */
INSERT INTO TRUNTEST  VALUES(5)
/*SELECT TABLE*/
SELECT * FROM TRUNTEST
/* BEFORE SELECT BEGIN TRAN*/
BEGIN TRAN
TRUNCATE TABLE TRUNTEST
SELECT * FROM TRUNTEST
/*TRUNCATE AFTER ROLLBACK TRANSACTION*/
ROLLBACK TRAN
SELECT * FROM TRUNTEST
/*COMMIT TRANSACTION*/
BEGIN TRAN
COMMIT TRAN
SELECT * FROM TRUNTEST

SQL QUERY TO FIND THE 5TH MAX MARK FROM EXAM TABLE

SELECT Top 1 MARK AS MARK FROM exam

WHERE MARK IN

(SELECT DISTINCT TOP(5) MARK FROM exam ORDER BY MARK DESC)

ORDER BY MARK

Query to find Name of the day

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)

RETURNS VARCHAR(10)

AS

BEGIN

DECLARE @rtDayofWeek VARCHAR(10)

SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)

WHEN 1 THEN 'Sunday'

WHEN 2 THEN 'Monday'

WHEN 3 THEN 'Tuesday'

WHEN 4 THEN 'Wednesday'

WHEN 5 THEN 'Thursday'

WHEN 6 THEN 'Friday'

WHEN 7 THEN 'Saturday'

END

RETURN (@rtDayofWeek)

END

GO

SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek

SQL QUERY TO FIND UNUSED TABLES & STORED PROCEDURES FROM A DB

Hi i had added the below query to find the unused tables and stored procedures from data base.first select the database and then write this query.

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID

FROM SYS.INDEXES AS I

INNER JOIN SYS.OBJECTS AS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID

NOT IN (SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(db_name()))

ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC.

Expecting your comments.