Create function to calculate age on specific date

 This function will take 2 datetime parameters, the DOB, and a date to check the age at.

CREATE FUNCTION [dbo].[Calc_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END

Check the age:

SELECT dbo.Calc_Age('2010-04-13',Getdate())

Output

11

Create Employee and Manager Hierarchy using Common Table Expression

We will see how to get the Employee-Manager hierarchy from the same table.

Let's setup the table first.

CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)

insert values into the table

INSERT INTO Employee VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employee VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employee VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employee VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employee VALUES (105, 'Zydr', 'Klyb', 101)
INSERT INTO Employee VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employee VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employee VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employee VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employee VALUES (110, 'Michael', 'Kensington', 106)

See the values below.

EmployeeID	FirstName	LastName	ManagerID
101	Ken	Sánchez	NULL
102	Keith	Hall	101
103	Fred	Bloggs	101
104	Joseph	Walker	102
105	Zydr	Klyb	101
106	Sam	Jackson	105
107	Peter	Miller	103
108	Chloe	Samuels	105
109	George	Weasley	105
110	Michael	Kensington	106

Below SQL Code to demonstrate the Employee and Manager relationship.

;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, EmpLevel) AS
(
	SELECT EmployeeID, FirstName, LastName, ManagerID, 1
	FROM Employee
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
	FROM Employee AS e
	INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employee WHERE EmployeeID = cteReports.SupervisorID)
AS ManagerName
FROM cteReports
ORDER BY EmpLevel, SupervisorID

Output:

FullName	EmpLevel	ManagerName
Ken Sánchez	1	NULL
Keith Hall	2	Ken Sánchez
Fred Bloggs	2	Ken Sánchez
Zydr Klyb	2	Ken Sánchez
Joseph Walker	3	Keith Hall
Peter Miller	3	Fred Bloggs
Sam Jackson	3	Zydr Klyb
Chloe Samuels	3	Zydr Klyb
George Weasley	3	Zydr Klyb
Michael Kensington	4	Sam Jackson

How to delete duplicate rows using CTE

 Duplicate records cane be deleted in several ways in SQL Server. Delete the duplicate records using common table expression is one of method.

Let's see the below example.

Employee Table:

ID	FirstName	LastName	Gender	Salary
1	Mark	Hastings	Male	60000
1	Mark	Hastings	Male	60000
2	Mary	Lambeth	Female	30000
2	Mary	Lambeth	Female	30000
3	Ben	Hoskins	Male	70000
3	Ben	Hoskins	Male	70000
3	Ben	Hoskins	Male	70000

SQL Code:

WITH EmployeeCTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
FROM Employee
)
DELETE FROM EmployeeCTE WHERE RowNumber > 1

Output:

ID	FirstName	LastName	Gender	Salary
1	Mark	Hastings	Male	60000
2	Mary	Lambeth	Female	30000
3	Ben	Hoskins	Male	70000

Display column names separated with comma

We can achieve this by STUFF Function. I have used it as stored procedure.

See Detail about STUFF Function: STUFF Function with example

SQL Code:

CREATE PROCEDURE TableColumns (@Table VARCHAR(100))
AS
SELECT ColumnNames =
STUFF( (SELECT ', ' + c.name
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID( @Table)
FOR XML PATH('')),
1, 1, '')
GO

Execute and run the above store procedure.

exec TableColumns 'Employee_Details'

Output:

ColumnNames

 EmployeeId, Name, SSN, DepartmentCode, IntraDepartmentCode

Retrieve all Table detail from Known Column in SQL Server

This query will return all COLUMNS and their associated TABLES for a given column name.

It is designed to show you what are the tables (unknown) contain a specified column (known)

SQL Code:

USE AdventureWorks2017
GO

SELECT
c.name AS ColName,
t.name AS TableName
FROM
sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE
c.name LIKE 'Departmentid' --provide known column name here

Output:

Related Article:

• Retrieve information on backup and restore operations in SQL Server
• Return list of SQL Agent jobs with schedule information in SQL Server
• Count the Number of Tables in a Database in SQL Server
• Retrieve List of all Stored Procedures in SQL Server
• Display all data files for all databases with size and growth info in SQL Server
• Get all schemas, tables, columns and indexes in SQL Server
• Retrieve all Table detail from Known Column in SQL Server
• Get the list of all databases on a server in SQL Server
• Show Size of All Tables in Current Database in SQL Server

Get the list of all databases on a server in SQL Server

Find List of all databases in sql server use the below methods. 

Method 1: 

Below query will be applicable for SQL Server 2000+ version (Contains 12 columns)

SELECT * FROM dbo.sysdatabases

Method 2: 

Below query extract information about databases with more information (ex: State, Isolation, recovery model etc.)

Note: This is a catalog view and will be available SQL SERVER 2005+ versions

SELECT * FROM sys.databases

Method 3: 

To see just database names you can use undocumented sp_MSForEachDB

EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'

Method 4: 

Below SP will help you to provide database size along with databases name , owner, status etc. on the server.

EXEC sp_helpdb

Method 5 

Similarly, below stored procedure will give database name, database size and Remarks

EXEC sp_databases

Related Article:

• Retrieve information on backup and restore operations in SQL Server
• Return list of SQL Agent jobs with schedule information in SQL Server
• Count the Number of Tables in a Database in SQL Server
• Retrieve List of all Stored Procedures in SQL Server
• Display all data files for all databases with size and growth info in SQL Server
• Get all schemas, tables, columns and indexes in SQL Server
• Retrieve all Table detail from Known Column in SQL Server
• Get the list of all databases on a server in SQL Server
• Show Size of All Tables in Current Database in SQL Server