Post
Ask Question

Database Dictionary (1-2) --- Create a System Data Dictionary Database

Note: this article is published on 09/24/2024.

This series of articles will discuss Database Dictionary related issues

Introduction:

In this article, we will Create a SDD Database. SDD stands for System (Server) Data Dictionary. It will includes tables:

  • Servers
  • Databases
  • Schemata
  • Tables
  • Columns

Each table will includes all relative items in the whole SQL Server Instance. The primary key of servers will be the foreign key of databases, the primary key of databases will be the foreign key of schemata, and so on so forth. This database will be universal and can be used in any SQL Server Instance (could be multiple instances).

This article will include the contents

  • A - Indroduction
  • B - Create Tables
    • Create Server Table
    • Create Database Table
    • Create Schema Table
    • Create Table Table
    • Create Column Table
  • C - Create Procedures to feed the tables
    • Create PROCEDURE [dbo].[Server] 
    • Create PROCEDURE [dbo].[Database] 
    • Create PROCEDURE [dbo].[Schema]
    • Create PROCEDURE [dbo].[Table] 
    • Create PROCEDURE [dbo].[Column] 
  • D.- Control Batch

B - Create Tables

Create Server Table

USE [SDD]
GO

/****** Object:  Table [dbo].[Servers]    Script Date: 9/14/2024 5:45:13 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Servers](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[ServerName] [NVARCHAR](MAX) NOT NULL,
 CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Create Database Table

USE [SDD]
GO

/****** Object:  Table [dbo].[Databases]    Script Date: 9/14/2024 5:46:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Databases](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [NVARCHAR](MAX) NOT NULL,
	[Database_Id] [INT] NOT NULL,
	[ServerId] [INT] NOT NULL,
 CONSTRAINT [PK_Databases] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Databases]  WITH CHECK ADD  CONSTRAINT [FK_ServerDatabase] FOREIGN KEY([ServerId])
REFERENCES [dbo].[Servers] ([Id])
GO

ALTER TABLE [dbo].[Databases] CHECK CONSTRAINT [FK_ServerDatabase]
GO

Create Schema Table

USE [SDD]
GO

/****** Object:  Table [dbo].[Schemata]    Script Date: 9/14/2024 5:47:56 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Schemata](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[SchemaName] [NVARCHAR](MAX) NOT NULL,
	[Schema_Id] [INT] NOT NULL,
	[DatabaseId] [INT] NOT NULL,
 CONSTRAINT [PK_Schemata] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Schemata]  WITH CHECK ADD  CONSTRAINT [FK_DatabaseSchema] FOREIGN KEY([DatabaseId])
REFERENCES [dbo].[Databases] ([Id])
GO

ALTER TABLE [dbo].[Schemata] CHECK CONSTRAINT [FK_DatabaseSchema]
GO

Create Table Table

USE [SDD]
GO

/****** Object:  Table [dbo].[Tables]    Script Date: 9/14/2024 5:48:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tables](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[TableName] [NVARCHAR](MAX) NOT NULL,
	[Table_Id] [INT] NOT NULL,
	[SchemaId] [INT] NOT NULL,
 CONSTRAINT [PK_Tables] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tables]  WITH CHECK ADD  CONSTRAINT [FK_SchemaTable] FOREIGN KEY([SchemaId])
REFERENCES [dbo].[Schemata] ([Id])
GO

ALTER TABLE [dbo].[Tables] CHECK CONSTRAINT [FK_SchemaTable]
GO

Create Column Table

USE [SDD]
GO

/****** Object:  Table [dbo].[Columns]    Script Date: 9/14/2024 10:54:52 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Columns](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[ColumnName] [NVARCHAR](MAX) NOT NULL,
	[Column_Id] [INT] NOT NULL,
	[TableId] [INT] NOT NULL,
 CONSTRAINT [PK_Columns] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Columns]  WITH CHECK ADD  CONSTRAINT [FK_TableColumn] FOREIGN KEY([TableId])
REFERENCES [dbo].[Tables] ([Id])
GO

ALTER TABLE [dbo].[Columns] CHECK CONSTRAINT [FK_TableColumn]
GO

C - Create Procedures to feed the tables

Create PROCEDURE [dbo].[Server] 

Servers could be procedured, but due to we only have one server so far, we just insert the record into the table:

Create PROCEDURE [dbo].[Database] 

USE [SDD]
GO

/****** Object:  StoredProcedure [dbo].[Database]    Script Date: 9/20/2024 12:36:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Database]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql NVARCHAR(MAX) = N'';
	DECLARE @sqlBody NVARCHAR(MAX) = N'';

	-- Execution for one database
	SELECT @sqlBody =
	'
		INSERT INTO SDD.dbo.Databases
		SELECT d.name, d.database_id, s.id
		FROM
			sys.databases d
		INNER JOIN
			SDD.dbo.Servers s
		ON
			s.Id = 1
		AND 
			d.name = DB_NAME()
	'

	-- Loop all databases
	SELECT @sql += 'USE ' + QUOTENAME(name) + @sqlBody
	FROM sys.databases
	WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

	-- Execution for the all databases
	EXEC sp_executesql @sql;

END
GO

Result:

Create PROCEDURE [dbo].[Schema]

USE [SDD]
GO

/****** Object:  StoredProcedure [dbo].[Schema]    Script Date: 9/20/2024 12:37:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Schema]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql NVARCHAR(MAX) = N'';
	DECLARE @sqlBody NVARCHAR(MAX) = N'';

	-- Execution for one database
	SELECT @sqlBody =
	'
		INSERT INTO SDD.dbo.Schemata
		SELECT s.name, s.schema_id, d.id
		FROM
			sys.schemas s
		INNER JOIN
			SDD.dbo.Databases d
		ON
			d.DatabaseName = DB_NAME()
		AND 
			s.name IN (
						SELECT TABLE_SCHEMA
						FROM INFORMATION_SCHEMA.TABLES
					   )
	'

	-- Loop all databases
	SELECT @sql += 'USE ' + QUOTENAME(name) + @sqlBody
	FROM sys.databases
	WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

	-- Execution for the all databases
	EXEC sp_executesql @sql;

END
GO

Result:

with 44 schemata:

Create PROCEDURE [dbo].[Table] 

USE [SDD]
GO

/****** Object:  StoredProcedure [dbo].[Table]    Script Date: 9/20/2024 12:39:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Table]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql NVARCHAR(MAX) = N'';
	DECLARE @sqlBody NVARCHAR(MAX) = N'';

	-- Execution for one database
	SELECT @sqlBody =
	'
		INSERT INTO SDD.dbo.Tables

		SELECT t.name, t.object_id, s.Id
		FROM
			sys.tables t
		INNER JOIN
			SDD.dbo.Databases d
		ON
			d.DatabaseName = DB_NAME()
		INNER JOIN
			SDD.dbo.Schemata s
		ON
			s.DatabaseId = d.Id
		AND
			t.schema_id = s.Schema_Id
	'

	-- Loop all databases
	SELECT @sql += 'USE ' + QUOTENAME(name) + @sqlBody
	FROM sys.databases
	WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

	-- Execution for the all databases
	EXEC sp_executesql @sql;


END
GO

Result:

with totally 3309 tables

Create PROCEDURE [dbo].[Column] 

USE [SDD]
GO

/****** Object:  StoredProcedure [dbo].[Column]    Script Date: 9/20/2024 12:39:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Column]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql NVARCHAR(MAX) = N'';
	DECLARE @sqlBody NVARCHAR(MAX) = N'';

	-- Execution for one database
	SELECT @sqlBody =
	'
		INSERT INTO SDD.dbo.Columns

		SELECT c.name, c.column_id, t.Id
		FROM
			sys.all_columns c
		INNER JOIN
			SDD.dbo.Databases d
		ON
			d.DatabaseName = DB_NAME()
		INNER JOIN
			SDD.dbo.Schemata s
		ON
			s.DatabaseId = d.Id
		INNER JOIN
			SDD.dbo.Tables t
		ON
			t.SchemaId = s.Id
		AND
			c.object_id = t.Table_Id
		AND
			c.name IN (
					SELECT COLUMN_NAME
					FROM INFORMATION_SCHEMA.COLUMNS
					)
	'

	-- Loop all databases
	SELECT @sql += 'USE ' + QUOTENAME(name) + @sqlBody
	FROM sys.databases
	WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');

	-- Execution for the all databases
	EXEC sp_executesql @sql;

END
GO

Result:

with totally 141689 columns in this SQL instance:

D - Control Batch

These batch command will hlep to show the tables, to delete and rebuid the tables. Due to the Primary Key and Foreign Key relationship, the tables created must be one by one through the order of Servers, Databases, Schemata, Tables and Columns, while delete or trancate the database must follow the opposite order.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT * FROM [SDD].[dbo].[Servers]
SELECT * FROM [SDD].[dbo].databases
ORDER BY databasename
SELECT * FROM [SDD].[dbo].schemata
WHERE databaseId = 21
ORDER BY schemaname
SELECT * FROM [SDD].[dbo].tables
WHERE sdd.dbo.tables.tablename = 'DOC_locations'
SELECT * FROM [SDD].[dbo].columns

DELETE FROM SDD.dbo.Columns
DELETE FROM SDD.dbo.Tables
DELETE FROM SDD.dbo.Schemata
DELETE FROM SDD.dbo.Databases
DBCC CHECKIDENT('SDD.dbo.Columns',RESEED,0)
DBCC CHECKIDENT('SDD.dbo.Tables',RESEED,0)
DBCC CHECKIDENT('SDD.dbo.Schemata',RESEED,0)
DBCC CHECKIDENT('SDD.dbo.Databases',RESEED,0)
EXEC SDD.dbo.[Database]
EXEC SDD.dbo.[Schema]
EXEC SDD.dbo.[Table]
EXEC SDD.dbo.[Column]

 

 


Similar Articles