What is the Database structure (Schema)?

Products

• IT Management Suite
• Client Management Suite
• Server Management Suite
• Asset Management Suite

Issue/Introduction

What is the Database structure (schema) of the Notification Server or any other database?

Resolution

These are SQL scripts to show and capture the Database structure of the Notification Server.

 

---

-- SQL Script to capture the database table structure

select
[table_name] as  [Table Name],
[column_name] as [Column Name],
case [data_type]
  when 'varchar' then [data_type] + '(' + cast([character_maximum_length]  as varchar) + ')'
  when 'nvarchar' then [data_type] + '(' + cast([character_maximum_length]  as nvarchar) + ')'
  else [data_type]
end as [Data Type],
case [is_nullable]
  when 'No' then 'No'
  else 'Yes'
end as [Nullable],
isnull([column_default], '') as [Default Value],
isnull(cast([numeric_precision] as nvarchar),'') as [Precision],
isnull(cast([numeric_precision_radix] as nvarchar),'') as [Precision Radix],
isnull([collation_name],'') as [Collation Name]
from information_schema.[columns]
where [table_catalog]   like '%'
    and [table_schema]  like 'dbo'
    and [table_name]     like '%'
    and [column_name]  like '%'
    and [data_type]        like '%'
order by [table_name], [ordinal_position]

 

---

 

-- SQL Script to capture the names of the database objects

select distinct
case [xtype]
 when 'C'   then 'CHECK constraint'
 when 'D'   then 'DEFAULT constraint'
 when 'F'    then 'FOREIGN KEY constraint'
 when 'L'    then 'Log'
 when 'FN' then 'Scalar function'
 when 'IF'   then 'Inlined table-function'
 when 'P'    then 'Stored procedure'
 when 'PK' then 'PRIMARY KEY constraint'
 when 'RF'  then 'Replication filter stored procedure'
 when 'S'    then 'SYSTEM table'
 when 'TF'  then 'Table function'
 when 'TR'  then 'Trigger'
 when 'U'    then 'User table'
 when 'UQ' then 'UNIQUE constraint'
 when 'V'    then 'View'
 when 'X'    then 'Extended stored procedure'
end as [xtype],
[name] as [Name]
from [dbo].[sysobjects]
where [xtype] in ('C', 'D', 'FN','P', 'PK', 'TR', 'U', 'V')
  and [name] like '%'
order by [xtype], [name]

---

 

-- SQL Script to capture the Names and Definitions of Views

select
   [table_name] as [View Name],
   [view_definition] as [View Definition]
from information_schema.[views]
where [table_name] like '%'
order by [table_name]

---

-- SQL Script to capture the Names and Definitions of functions and procedures

select
   [routine_type] as [Routine Type],
   [routine_name] as [Routine Name],
   [routine_definition] as [Routine Definition]
from information_schema.[routines]
where [routine_type] in ('FUNCTION','PROCEDURE')
    and [routine_name]  like '%'
order by [routine_name]

---

 

-- SQL Script to capture the Names and Parameters of functions and procedures

select
   [specific_name] as [Routine Name],
   [parameter_name] as [Parameter Name],
case [data_type]
   when 'varchar' then [data_type] + '(' + cast([character_maximum_length]  as varchar) + ')'
   when 'nvarchar' then [data_type] + '(' + cast([character_maximum_length]  as nvarchar) + ')'
   else [data_type]
end as [Data Type]
from information_schema.[parameters]
where [specific_name] like '%'
order by [specific_name], [ordinal_position]

---

 

-- SQL Script to capture the GUID and Table names

-- This could help your SQL joins on what table have what GUIDS.

select
[column_name] as [Guid Name],
[table_name] as  [Table Name],
case [is_nullable]
when 'YES' then 'Yes'
  else 'No'
end as [Nullable],
isnull([column_default], '') as [Default Value]
from information_schema.[columns]
where [table_schema]  = 'dbo'
  and [data_type] like 'uniqueidentifier'
order by [column_name], [table_name]

 

---

 

-- To look at the table or view definition

sp_help TableName
sp_help ViewName

sp_columns TableName

-- To look at what indexes are in a table

sp_helpindex TableName

-- To look at the definition of a stored procedure or a view

sp_helptext StoredProcedureName

sp_helptext ViewName

-- To look at the database object (tables, views, stored procedures) dependencies

sp_depends DatabaseObject

---

 

Notes:

• Run these SQL scripts in Query Analyzer and save the result to a CSV file for study. 
• You will need to select the database (Altiris, Altiris_Incidents, express …) in Query Analyzer you wish to capture the schema.
• In the where clause of the scripts, I left variables to filter (table_catalog, table_schema, table_name, column_name, data_type ...) and narrow your results.
• These are SQL scripts will show you any database structure (Schema), but will not show the relationships between the tables (Data Dictionary).
• The best joins for performance reasons will be on the columns that are indexed. Most table joins will be with uniqueidentifier (GUIDs) like (_ResourceGuid, Guid, ProductGuid, _KeyHash ...) or varchar/nvarchar (strings); so you may wish to filter on the data_type to see these.
• To look at a text output (Ctrl + T) or a table output (Ctrl + D) use the control key combinations. Then viewing the output from the stored procedure sp_helptext, it is good to change the output to text. 
• The attached files (DatabaseTableRelationByGuid.txt, DatabaseTableRelationByString.txt) are SQL scripts that will show you the possible relations between tables and columns depending on the data that is in you database. These SQL scripts can be slow, due to the use of nested cursors in the script. There are variables in the scripts to filter /narrow you results to a set of tables.