These are SQL scripts to show and capture the Database structure of the Notification Server.
-- SQL Script to capture the names of the database objects
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]
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
[table_name] as [View Name],
[view_definition] as [View Definition]
where [table_name] like '%'
order by [table_name]
-- SQL Script to capture the Names and Parameters of functions and procedures
[specific_name] as [Routine Name],
[parameter_name] as [Parameter Name],
when 'varchar' then [data_type] + '(' + cast([character_maximum_length] as varchar) + ')'
when 'nvarchar' then [data_type] + '(' + cast([character_maximum_length] as nvarchar) + ')'
end as [Data Type]
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.
[column_name] as [Guid Name],
[table_name] as [Table Name],
when 'YES' then 'Yes'
end as [Nullable],
isnull([column_default], '') as [Default Value]
where [table_schema] = 'dbo'
and [data_type] like 'uniqueidentifier'
order by [column_name], [table_name]
-- To look at the table or view definition
-- To look at what indexes are in a table
-- To look at the definition of a stored procedure or a view
-- To look at the database object (tables, views, stored procedures) dependencies
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.