How to get the Table Metadata Information using a Query in SQL Server

SQL Procedure to get the Metadata Information for a Table in SQL Server
Scenario:
We can get the Metadata information of a Table like Schema Name, Table ID, Table Name, PK Constraint, Column Name, Column Data Type, Column Is Nullable, Column Length, FK Constraint, Reference Table ID, Ref Table Name and Reference Column Name details using the below Stored Procedure.
This Procedure have a Parameter, which is nothing but the Table Name which we needs to pass to get the Metadata information for that Table.

USE AnalyticsDB
GO

CREATE PROCEDURE [dbo].[sp_Get_Table_Metadata]                           
    ( 
   --Table Parameter                       
     @vTable_Name AS NVARCHAR(500) = NULL                    
    )                          
AS  
BEGIN
SELECT S1.[Schema],S1.[Table_ID],S1.[Tbl_Name],S1.[PK_Constraint], S1.[Col_Name],
S1.[Data_Type], S1.[Is_Nullable], S1.[Col_Length], S2.[FK_Constraint], S2.Ref_Table_ID,
S2.[Ref_Tbl_Name], S2.[Ref_Col_Name]
FROM
(
SELECT  Sch.Name AS [Schema],
TB.Object_ID AS [Table_ID],
TB.Name AS [Tbl_Name],
      --ISNULL(I.Is_Primary_Key,0) AS [Is_Primary_Key],
I.Name AS [PK_Constraint],
C.Name AS [Col_Name],
TY.Name AS [Data_Type],
(CASE WHEN C.Is_Nullable=1 THEN 'Y' ELSE 'N' END) [Is_Nullable],
C.Max_Length AS [Col_Length]
     --C.Precision [Precision],
     --C.Scale [Scale]
FROM Sys.Tables TB
INNER JOIN
Sys.Columns  C ON TB.Object_ID=C.Object_ID
LEFT JOIN 
Sys.Types TY ON C.User_Type_ID=TY.User_Type_ID
LEFT JOIN
Sys.Index_Columns IC ON IC.Object_ID=C.Object_ID AND IC.Column_ID=C.Column_ID
LEFT JOIN
Sys.Indexes I ON IC.Object_ID=I.Object_ID AND IC.Index_ID=I.Index_ID
LEFT JOIN
Sys.Schemas Sch ON TB.Schema_ID=Sch.Schema_ID
WHERE TB.Type='U' AND TB.Name <> 'sysdiagrams'
AND TB.Object_ID=Object_ID(@vTable_Name)
 ) S1
LEFT JOIN
 (
SELECT  Sch.Name AS [Schema],
T1.Object_ID as [Table_ID],
T1.Name AS [Tbl_Name],
Obj.Name AS [FK_Constraint],
C1.Name AS [FK_Col_Name],
T2.Object_ID AS [Ref_Table_ID],
T2.Name AS [Ref_Tbl_Name],
C2.Name AS [Ref_Col_Name]
FROM Sys.Foreign_Key_Columns FK
INNER JOIN Sys.Objects Obj
ON Obj.Object_ID = FK.Constraint_Object_ID
INNER JOIN Sys.Tables T1
ON T1.Object_ID = FK.Parent_Object_ID
INNER JOIN Sys.Schemas Sch
ON T1.Schema_ID = Sch.Schema_ID
INNER JOIN Sys.Columns C1
ON C1.Column_ID = Parent_Column_ID AND C1.Object_ID = T1.Object_ID
INNER JOIN Sys.Tables T2
ON T2.Object_ID = FK.Referenced_Object_ID
INNER JOIN Sys.Columns C2
ON C2.Column_ID= Referenced_Column_ID AND C2.Object_ID = T2.Object_ID
Where T1.Object_ID=Object_ID(@vTable_Name)
) S2
ON S1.[Table_ID]=S2.[Table_ID] AND S1.[Col_Name]=S2.[FK_Col_Name]
END

GO

Now will run this Procedure and see the Metadata information of a Table '[dbo].[FactSales]'.
EXEC [dbo].[sp_Get_Table_Metadata]  '[dbo].[FactSales]'

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------