You want to be able to create your own reports, but would like to understand how the database has been put together first in order to make your database object selections easier. For instance how do you get the:
ITMS 8.x
The following information is provided as a reference since we don't provide detailed information regarding our database schema. So how exactly is this information obtained? There are many examples online that can be used to get such details such as the following Microsoft article:
Method 1
Using queries to display and save the information:
--/ data classes
SELECT vi.[ProductGuid],vp.[Name] AS [Product Name],dc.[Name] AS [DataClass Name],dca.AttrName AS [Column Name]
FROM DataClass dc JOIN DataClassAttribute dca
ON dca.InvClassId = dc.[id]
JOIN vItem vi
ON vi.[Guid] = dc.[Guid]
JOIN vProduct vp
ON vp.[ProductGuid] = vi.[ProductGuid]
ORDER BY [Product Name]
_____________________________________
--/ Table structure
SELECT table_name as 'Table Name',
column_name as 'Column Name',
data_type as 'Data Type',
character_maximum_length as 'Char Max Length',
column_default as 'Default Value',
is_nullable as 'Nullable',
numeric_precision as 'Precision',
numeric_precision_radix as 'Precision Radix'
FROM information_schema.columns
WHERE table_catalog = 'Altiris'
AND table_schema = 'dbo'
ORDER BY table_name, ordinal_position
---------------------------
--Note:
--Run this in Query Analyzer and save the result to a csv file.
--This is a SQL script to snag any database table structure (Schema).
--This script will not show the relationships between the tables (Data Dictionary).
--If the database is not to the default name, then change the name in the script.
--If you change the where clause to " where table_catalog = 'Altiris_Incidents' ", you can get the HD database table structure.
--Most table joins will be with uniqueidentifier or Guids (_ResourceGuid, Guid, ProductGuid ...)
_______________________________
--/ Tables and columns
SELECT
o.[Name] AS [Table/View],
c.[Name] AS [Column]
FROM [sysobjects] o
JOIN [syscolumns] c
ON o.[id] = c.[id]
WHERE o.[id] > 100
AND c.[Name] NOT LIKE '@%'
AND o.[Name] NOT IN ('sysconstraints','syssegments')
ORDER BY o.[Name] ASC
Method 2
Using SQL Server Management Studio:
Click Next and the scripts will be generated automatically.
Click Finish to close the wizard.
Important Note: The generated script will contain the table name and the corresponding columns and indexes. The generated script can be used to reference what is available for each table.