Article ID: 1485
These are SQL scripts to show and capture the Database structure of the Notification Server or any other database.
-- 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 proceduresselect
[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 withuniqueidentifier (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.
