Part 1: How to Reverse Engineer your SQL Server Database, using the SYS objects.

How to use the SYS objects to reverse engineer your database design.

The following object are used in this PART 1 video:

Table of Contents

Reverse Engineer your database.

#softwareNuggets, #sqlServer, #reverseEngineer, #database, #databaseTables, #DBTables, #SysTypes

[SoftwareNuggets]

use AdventureWorks2019

--get the name of the server you are working with


--get top 15 objects
select top 15 *
from sys.objects obj (nolock)


--let's group objects
select [type], type_desc, count(*) num_of_objects
from sys.objects obj (nolock)
group by [type], type_desc
order by type_desc


--show all the tables
select [type], type_desc, obj.name
from sys.objects obj (nolock)
where obj.type_desc = 'USER_TABLE'
order by obj.name


select *
from sys.schemas (nolock)




select
sch.name as SchemaName,
obj.name as TableName,
obj.object_id as TableObjId
from sys.objects obj (nolock)
join sys.schemas sch (nolock)
on (sch.schema_id = obj.schema_id)
where obj.type_desc = 'USER_TABLE'
and sch.name like 'Hum%'
order by sch.name, obj.name


--the object_id on your computer will be different




select top 5 *
from sys.columns (nolock)
where object_id = 1701581100



--make sure you use the correct object_id
--1701581100 is on my computer
--yours will different
select
col.[object_id],
col.[name] ColumnName,
col.column_id OrdinalPosition,
col.system_type_id,
styp.[name] SysDataType,
col.user_type_id,
typ.[name] UserDefinedDataType,
col.max_length,
col.is_identity,
col.is_computed,
col.is_rowguidcol,
col.is_nullable,
col.[precision],
col.scale
from sys.columns col
join sys.systypes typ
on(typ.xusertype = col.user_type_id)
join sys.systypes styp
on(styp.xusertype = col.system_type_id)
where col.object_id = 1701581100
order by col.column_id


--make sure you use the correct object_id
--1701581100 is on my computer
--yours will different
select
col.[object_id],
col.[name] ColumnName,
col.column_id OrdinalPosition,
col.system_type_id,
styp.[name] SysDataType,
col.user_type_id,
typ.[name] UserDefinedDataType,
col.max_length,
case when col.is_identity = 1 then 'Y' else 'N' end as is_identity_col,
case when col.is_computed = 1 then 'Y' else 'N' end as is_computed_col,
case when col.is_rowguidcol = 1 then 'Y' else 'N' end as is_rowguid_col,
case when col.is_nullable = 1 then 'N' else 'Y' end as is_required,
col.[precision],
col.scale
from sys.columns col
join sys.systypes typ
on(typ.xusertype = col.user_type_id)
join sys.systypes styp
on(styp.xusertype = col.system_type_id)
where col.object_id = 1701581100
order by col.column_id


--make sure you use the correct object_id
--1701581100 is on my computer
--yours will different
select *
from sys.extended_properties ep
where ep.major_id = 1701581100
and ep.minor_id in(1, 2, 3, 4)
and class = 1







-- get the check constraints
--make sure you use the correct parent_id
--2099048 is on my computer
--yours will different
select object_id,
[name] as constraint_name,
parent_object_id,
type,
type_desc,
definition,
parent_column_id
from sys.check_constraints (nolock)
where parent_object_id=2099048



--check constraints
--make sure you use the correct parent_id
--1893581784 is on my computer
--yours will different
select *
from sys.objects (nolock)
where parent_object_id = 1893581784
and type = 'C'





--check constraints
--let's get the definition
--make sure you use the correct parent_id
--1893581784 is on my computer
--yours will different
select
cc.object_id,
cc.name as ChkConstraintName,
o.name as TableName,
cc.definition
from sys.objects o
join sys.check_constraints cc
on(cc.parent_object_id = o.object_id)

where cc.parent_object_id = 1893581784
order by cc.name


--get all check constraints definitions
select definition
from sys.check_constraints

[DianaUrbina-bdmk]

Thank you! This video was super helpful.

[helloyou]

Hey, nice and precise tutorial.
One minor suggestion: it would be nice if the code could be copied from somewhere (description/repository) for all queries or for complex/long ones.

I get that it's very helpful to learn by writing the queries manually, but timewise it's more convenient to copy/paste them and rerun locally.

But nonetheless, thanks for the compact tutorial!