NS 6 Database – Everything You Wanted to Know and Probably More

The Notification Server database can seem like a daunting place, where you wonder how you will ever find what you are looking for. I've gathered together some scripts and other information that will be useful in diving deeper into the database.

You can run these SQL scripts in Query Analyzer and save the results to file for review later on. Also be sure to select the Altiris database (or whatever database you want to look at) in Query Analyzer, otherwise you will likely get errors. You can modify the where clause of the scripts, to filter (table_catalog, table_schema, ...) and narrow your results.

• NS Database table structure
• NS Database object names
• NS Database view names and definitions
• NS Database function and procedure names and definitions
• NS Database function and procedure names and parameters
• NS Database tables and table guids
• NS Database table and view definitions
• NS Database table indexes
• NS Database stored procedure or view definition
• NS Database object dependencies

NS Database table structure

This query will gather the table name, column name, Data Type, if it is nullable, default value (if any), precision, precision radix and the collation. This will give you an overview of information that normally you would have to gather by looking at each individual table. Have the data type is particularly helpful for finding out how the data is stored for that specific column.

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]

NS Database object names

The results of this query would probably be most helpful if you save the results for quick reference in an excel spreadsheet. I would recommend putting each type in its own tab. Most helpful to me are the views and the stored procedure. You mileage may vary.

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]

NS Database view names and definitions

Once again this would be a great reference for how a specific view is defined. You could manually look at this information by view, but when looking for specific information (such as patch management tables) you can quickly find where the actual data is sorted.

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] 

NS Database function and procedure names and definitions

This shows a reference for how a function or procedure is defined. You could manually look at this information, but this will no doubt save you some time.

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]

NS Database function and procedure names and parameters

If you want to run a specific function or procedure but are unsure as to what parameters you need to pass to it, this will quickly gather this information for you.

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]

NS Database tables and table guids

I've asked myself a million times why the guid is not the same for every view and table. One time it is _Guid and other it is _ResourceGuid. This script with show you the guid for each table and should be really helpful when writing a query based on those more obscure tables. It will help you SQL joins.

SQL Script to capture the Guid and Table names

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]

NS Database table and view definitions

This query will gather all kinds of useful information for your favorite table or view, including the owner, type, create time along with other great information.

To look at the table or view definition

sp_help TableName 
sp_help ViewName
sp_columns TableName 

NS Database table indexes

This query gives you the name, description and the index keys.

To look at what indexes are in a table

 
sp_helpindex TableName

NS Database stored procedure or view definition

This isn't as useful for a lot of the stored procedures, but for those that are well documented you may find some value, otherwise it will simply show you how the procedure is created.

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

sp_helptext StoredProcedureName
sp_helptext ViewName

NS Database object dependencies

This will show any dependencies for a specific table, view or stored procedure.

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

sp_depends DatabaseObject 

I hope this helps you as you delve deeper into the Altiris Notification Server Database. It should be useful for writing SQL scripts, reports and other fun SQL functions.