480
Problem
There are situations when we need the structure of a SQL Server database and database objects for further processing. For example, when we want to create a skeleton of the database in a different location (i.e. computer, SQL Server, etc.), or when we want to make sure that the database structure has not been altered.
Solution
There are several ways of obtaining database schema. Sometimes it is important how we arrange the information, because when we process it later on it will be easier compared to the case when we have the information in a “messy” format.
In this tip we present how to get SQL Server columns, primary keys, other constraints and indexes and present the data in a JSON format. Also, it is not difficult to obtain other database objects (like stored procedures, etc.) in a similar way.
Generate SQL Server Table Structure in JSON Format
A nice layout of tables and their columns looks like:
{
"Table Schema": <schema name>,
"Tables": [
{
"Table Name": <table name>,
"Table Columns": [
{
"Column Name": <column name>,
"is_nullable": false,
"is_identity": true,
"Column Type": [
{
"data_type": "int",
"seed_value": 1,
"increment_value": 1
}
]
}
]
}
]
}
We group tables in the same schema, and columns in the same table.
The SQL SELECT statement which does this is:
select <br /> [Db Schema].TABLE_SCHEMA [Table Schema], <br /> [Tables].name [Table Name], <br /> [Table Columns].name [Column Name], <br /> [Table Columns].is_nullable [is_nullable], <br /> [Table Columns].is_identity [is_identity], <br /> [Table Columns].encryption_algorithm_name, <br /> [Table Columns].encryption_type_desc, <br /> case when cek.name is null then null else cek.name end as CEK_Name, <br /> [Column Type].name [data_type], <br /> cast<br /> (case when [Column Type].name = 'text'<br /> then null<br /> else <br /> case when [Table Columns].precision=0 and [Column Type].name <> 'text'<br /> then [Table Columns].max_length <br /> else null <br /> end<br /> end<br /> as smallint) [max_length], <br /> cast(case when [Table Columns].precision>0 and [Column Type].precision=[Column Type].scale <br /> then [Table Columns].precision else null end as tinyint) [precision], <br /> cast(case when [Table Columns].precision>0 and [Column Type].precision=[Column Type].scale <br /> then [Table Columns].scale else null end as tinyint) [scale], <br /> cast(case when [Table Columns].is_identity=1 <br /> then seed_value else null end as sql_variant) [seed_value], <br /> cast(case when [Table Columns].is_identity=1 <br /> then increment_value else null end as sql_variant) [increment_value], <br /> cast(case when [Table Columns].default_object_id>0 <br /> then definition else null end as nvarchar(4000)) [default_value] <br />from INFORMATION_SCHEMA.TABLES [Db Schema] <br /> join sys.objects [Tables] on [Db Schema].TABLE_SCHEMA = schema_name([Tables].[schema_id]) <br /> and [Db Schema].TABLE_NAME = [Tables].name<br /> join sys.columns [Table Columns] on [Tables].object_id=[Table Columns].object_id <br /> left join sys.column_encryption_keys cek <br /> on [Table Columns].column_encryption_key_id = CEK.column_encryption_key_id<br /> left join sys.identity_columns id on [Tables].object_id=id.object_id <br /> join sys.types [Column Type] on [Table Columns].system_type_id=[Column Type].system_type_id <br /> and [Column Type].system_type_id=[Column Type].user_type_id <br /> left join sys.default_constraints d on [Table Columns].default_object_id=d.object_id <br />where [Tables].type='u'<br />order by [Table Schema], [Table Name] <br />for json auto, root('DBColumns')
As you can see, we collect only partial information about a column. It is not difficult to include other column properties if necessary. Also, we collect information about encrypted columns, so we assume the SQL Server version is at least 2017
Saving a generated JSON file is easy and there are several ways how to do it.
Generate SQL Server Foreign Keys Structure in JSON Format
A nice layout of foreign keys looks like:
{
"Table Schema": <table schema>,
"Table Name": <table name>,
"Constraints": [
{
"Constraint Name": <FK name>,
"Referenced Schema": <referenced schema name>,
"Referenced Table": <referenced table name>,
"Columns": [
{
"COLUMN_NAME": <column name>,
"ordinal_position": 1,
"Referenced Column": 2
},
{
"COLUMN_NAME": <column name>,
"ordinal_position": 2,
"Referenced Column": 3
}
]
}
]
}
We group foreign keys in a similar way as for tables. Because a foreign key can refer to a key defined in another schema, we also need that information. Collecting the information in this format is more complex than for tables. The information we collect is the definition of the foreign key, such that we can easily replicate the foreign key if we want to create it in another database.
The SQL SELECT statement which does this is:
select distinct <br /> ConstraintColumns.TABLE_SCHEMA [Table Schema], <br /> ConstraintColumns.Table_Name [Table Name], <br /> [Constraints].CONSTRAINT_NAME [Constraint Name],<br /> [Constraints].[Referenced Schema],<br /> [Constraints].[Referenced Table], <br /> Columns.COLUMN_NAME, <br /> Columns.ordinal_position, Columns.[Referenced Column]
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ConstraintColumns
join
(<br /> select <br /> tc.TABLE_SCHEMA, <br /> tc.TABLE_NAME, <br /> tc.CONSTRAINT_NAME, <br /> rc.CONSTRAINT_SCHEMA [Referenced Schema],<br /> ccolumns.[Referenced Table]<br /> from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc<br /> join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc<br /> on tc.CONSTRAINT_SCHEMA=rc.CONSTRAINT_SCHEMA <br /> and tc.CONSTRAINT_NAME=rc.CONSTRAINT_NAME<br /> join <br /> (<br /> select<br /> schema_name(fk.schema_id) Table_Schema,<br /> object_name(fkc.parent_object_id) Table_Name, <br /> object_name(fkc.constraint_object_id) Constraint_Name, <br /> object_name(fkc.referenced_object_id) [Referenced Table]<br /> from sys.foreign_key_columns fkc <br /> join sys.foreign_keys fk on fk.object_id=fkc.constraint_object_id<br /> join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu <br /> on kcu.TABLE_NAME=object_name(fk.parent_object_id) <br /> and kcu.ORDINAL_POSITION=fkc.parent_column_id<br /> and kcu.CONSTRAINT_NAME=object_name(fkc.constraint_object_id)<br /> ) ccolumns <br /> on tc.TABLE_SCHEMA=ccolumns.TABLE_SCHEMA<br /> and tc.TABLE_NAME=ccolumns.TABLE_NAME <br /> ) [Constraints] <br /> on ConstraintColumns.CONSTRAINT_NAME=[Constraints].CONSTRAINT_NAME <br /> and ConstraintColumns.TABLE_NAME=[Constraints].TABLE_NAME<br />join <br /> (<br /> select <br /> ConstraintColumns.TABLE_SCHEMA,<br /> ConstraintColumns.TABLE_NAME, <br /> ConstraintColumns.COLUMN_NAME, <br /> ConstraintColumns.CONSTRAINT_NAME, <br /> ConstraintColumns.ordinal_position,<br /> ConstraintColumns.[Referenced Column]<br /> from <br /> (<br /> select<br /> schema_name(fk.schema_id) Table_Schema,<br /> object_name(fkc.parent_object_id) Table_Name, <br /> object_name(fkc.constraint_object_id) Constraint_Name, <br /> kcu.COLUMN_NAME, <br /> fkc.constraint_column_id ORDINAL_POSITION,<br /> fkc.parent_column_id [Referenced Column]<br /> from sys.foreign_key_columns fkc <br /> join sys.foreign_keys fk on fk.object_id=fkc.constraint_object_id<br /> join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu <br /> on kcu.TABLE_NAME=object_name(fk.parent_object_id) <br /> and kcu.ORDINAL_POSITION=fkc.parent_column_id<br /> and kcu.CONSTRAINT_NAME=object_name(fkc.constraint_object_id)<br /> ) ConstraintColumns<br /> ) Columns <br /> on ConstraintColumns.TABLE_SCHEMA=Columns.TABLE_SCHEMA<br /> and ConstraintColumns.TABLE_NAME=Columns.TABLE_NAME <br />order by ConstraintColumns.TABLE_SCHEMA, ConstraintColumns.Table_Name, <br /> [Constraint Name], [Referenced Schema], [Referenced Table], <br /> Columns.ordinal_position<br />for json auto, root('DBFKConstraints')
It was difficult to collect information about all constraints in one SELECT statement, but you can feel free to try.
Generate Other SQL Server Object Constraints Structure in JSON Format
Other constraints are primary keys, check constraints and unique keys. A nice layout of these constraints looks like:
{
"Table Schema": <schema name>,
"Table Name": <table name>,
"Constraints": [
{
"Constraint Name": <constraint name>,
"Constraint Type": "PRIMARY KEY",
"Columns": [
{
"COLUMN_NAME": <column name>,
"ordinal_position": 1
}
]
},
{
"Constraint Name": <constraint name>,
"Constraint Type": "UNIQUE",
"Columns": [
{
"COLUMN_NAME": <column name>,
"ordinal_position": 1
},
{
"COLUMN_NAME": <column name>,
"ordinal_position": 2
}
]
}
]
},
{
"Table Schema": <schema name>,
"Table Name": <table name>,
"Constraints": [
{
"Constraint Name": <constraint name>,
"Constraint Type": "CHECK",
"CHECK_CLAUSE": <check clause>,
"Columns": [
{
"COLUMN_NAME": <column name>
}
]
}
]
}
These constraints are also grouped in a similar way as foreign keys.
The SQL SELECT statement which does this is:
select distinct <br /> ConstraintColumns.TABLE_SCHEMA [Table Schema],<br /> ConstraintColumns.Table_Name [Table Name],<br /> [Constraints].CONSTRAINT_NAME [Constraint Name],<br /> [Constraints].CONSTRAINT_TYPE [Constraint Type],<br /> [Constraints].CHECK_CLAUSE,<br /> Columns.COLUMN_NAME,<br /> Columns.ordinal_position<br />from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ConstraintColumns<br />join <br /> (<br /> select tc.TABLE_SCHEMA, tc.CONSTRAINT_CATALOG, tc.CONSTRAINT_NAME, <br /> tc.CONSTRAINT_TYPE, tc.TABLE_NAME, ck.CHECK_CLAUSE<br /> from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc<br /> left join INFORMATION_SCHEMA.CHECK_CONSTRAINTS ck <br /> on tc.CONSTRAINT_CATALOG=ck.CONSTRAINT_CATALOG<br /> and tc.CONSTRAINT_NAME=ck.CONSTRAINT_NAME<br /> ) [Constraints] <br /> on ConstraintColumns.CONSTRAINT_NAME=[Constraints].CONSTRAINT_NAME <br /> and ConstraintColumns.CONSTRAINT_CATALOG=[Constraints].CONSTRAINT_CATALOG<br /> and ConstraintColumns.TABLE_NAME=[Constraints].TABLE_NAME<br />join <br /> (<br /> select CheckColumns.TABLE_SCHEMA,<br /> CheckColumns.TABLE_NAME, CheckColumns.COLUMN_NAME, <br /> CheckColumns.CONSTRAINT_NAME, null as ordinal_position<br /> from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CheckColumns <br /> where CheckColumns.CONSTRAINT_NAME in <br /> (<br /> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CHECK_CONSTRAINTS<br /> )<br /> union all<br /> select <br /> ConstraintColumns.TABLE_SCHEMA,<br /> ConstraintColumns.TABLE_NAME, ConstraintColumns.COLUMN_NAME, <br /> ConstraintColumns.CONSTRAINT_NAME, ConstraintColumns.ordinal_position<br /> from <br /> (<br /> select <br /> kcu.TABLE_SCHEMA,<br /> kcu.TABLE_NAME, <br /> kcu.CONSTRAINT_NAME, <br /> kcu.COLUMN_NAME, <br /> kcu.ORDINAL_POSITION<br /> from <br /> (<br /> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE <br /> where CONSTRAINT_NAME not in<br /> (<br /> select object_name([constraint_object_id])<br /> from sys.foreign_key_columns<br /> )<br /> ) kcu<br /> ) ConstraintColumns<br /> ) Columns <br /> on ConstraintColumns.TABLE_SCHEMA=Columns.TABLE_SCHEMA<br /> and ConstraintColumns.TABLE_NAME=Columns.TABLE_NAME <br /> and ConstraintColumns.CONSTRAINT_NAME=Columns.CONSTRAINT_NAME<br />order by <br /> ConstraintColumns.TABLE_SCHEMA, <br /> ConstraintColumns.Table_Name, <br /> [Constraint Name], <br /> Columns.ordinal_position<br />for json auto, root('DBConstraints')
As you can see, we collect not only the constraints names, but also their definitions, such that it is easy to replicate them in another database.
Generate SQL Server Index Structure in JSON Format
We use another query for indexes. A nice layout of indexes looks like:
{
"Table Schema": <schema name>,
"Table Name": <table name>,
"Indexes": [
{
"index_name": <index name>,
"index_type": "IX",
"type_desc": "NONCLUSTERED",
"ignore_dup_key": false,
"idxcol": [
{
"column_id": 1,
"column_name": <column name>,
"is_descending_key": false
}
]
},
{
"index_name": <index name>,
"index_type": "PK",
"type_desc": "CLUSTERED",
"ignore_dup_key": false,
"idxcol": [
{
"column_id": 1,
"column_name": <column name>,
"is_descending_key": false
}
]
},
{
"index_name": <index name>,
"index_type": "UQ",
"type_desc": "NONCLUSTERED",
"ignore_dup_key": false,
"idxcol": [
{
"column_id": 1,
"column_name": <column name>,
"is_descending_key": false
},
{
"column_id": 2,
"column_name": <column name>,
"is_descending_key": false
}
]
}
]
}
Indexes are also grouped in a similar way as the rest of elements presented above.
In SSMS the indexes shown for a particular table contain proper indexes (created with command CREATE INDEX), as well as primary keys and unique constraints. Our SELECT statement does the same.
The SQL SELECT statement is:
select<br /> schema_name(obj.schema_id) [Table Schema],<br /> obj.name [Table Name],<br /> [Indexes].name index_name,<br /> (<br /> case when is_primary_key=1<br /> then 'PK'<br /> else<br /> case when is_unique_constraint=1<br /> then 'UQ'<br /> else 'IX'<br /> end<br /> end<br /> ) index_type,<br /> [Indexes].type_desc,<br /> index_column_id [column_id],<br /> (<br /> select name from sys.columns cols <br /> where cols.object_id=obj.object_id and idxcol.column_id=cols.column_id<br /> ) column_name,<br /> ignore_dup_key,<br /> is_descending_key<br />from sys.indexes [Indexes]<br /> join sys.objects obj on obj.object_id=[Indexes].object_id and obj.type='u'<br /> join sys.index_columns idxcol on obj.object_id=idxcol.object_id<br /> and idxcol.index_id=[Indexes].index_id<br />where is_disabled=0<br />order by [Table Schema], [Table Name], index_name, column_id<br />for json auto, root('DBIndexes')
As you can see, we collect the definition for each index, such that it is easy to replicate them in another database.
Next Steps
- The queries above can be run on any SQL Server 2017 or newer. They were tested (and currently used) on SQL Server 2017 and 2019.
- The JSON file(s) can be created in several ways, one way is using SSMS: run the query against database of your choice, click on the link generated by the query and copy/paste the content opened in a new window to a Json file. You will need to format the result in order to get the format presented above. In another tip we will present a way of saving the schema using a C# program.
- These queries will be used in comparing two database structures and creating a new database based on the schema provided in the JSON file.
