Note: this article is published on 09/23/2024.
This series of articles will discuss Database Dictionary related issues
Introduction:
In this article, we will discuss the basic elements of database schema: Server, Database, Schema, Table and Column. We will discuss how to retrieve those infor from SQL Server.
- A - Introduction
- B - Data Source
- C - Retrieve Schema Info by SQL in SQL Server
- Get Servers (including linnked servers and remote servers) by SQL
- Get Databases by SQL
- Get Schemata by SQL
- Get Tables by SQL
- Get Columns by SQL
B - Data Source
Database data stored in system database:
![]()
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
![]()
Resource database location:
![]()
Resource Version:
![]()
Resource Last Update Date Time:
![]()
C - Retrieve Schema Info by SQL in SQL Server
Retrieve info from SQL server for:
- Server
- Database
- Schema
- Table
- Column
Get Servers (including linnked servers and remote servers) by SQL
SELECT * FROM sys.servers
SELECT * FROM sysservers
SELECT * FROM sys.sysservers
Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0. Such as
![]()
and
![]()
Get Databases by SQL
SELECT * FROM sys.databases
SELECT * FROM sys.sysdatabases
Contains one row per database in the instance of SQL Server.
![]()
and
![]()
Note:
Besids getting database Servers and databases, all other sys.command are running within a database:
![]()
Get Schemata by SQL
SELECT * FROM sys.schemas
SELECT COUNT(*) FROM sys.schemas
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA
SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME IN (
SELECT DISTINCT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
)
SELECT * FROM sys.schemas
WHERE name IN (
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
)
Such as
![]()
and
![]()
Get Tables by SQL
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
SELECT name
FROM sys.tables
SELECT name
FROM sysobjects
WHERE xtype = 'U'
SELECT name
FROM sys.objects
WHERE type_desc = 'USER_TABLE'
such as
![]()
and
![]()
and
![]()
and
![]()
and
![]()
Get Columns by SQL
SELECT * FROM sys.columns
SELECT * FROM sys.all_columns
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
such as
![]()
and
![]()
References: