How to extract database schema information?

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
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:

Table and column listings?
Table and column associated with their products?
Object definitions?

Answer

--/ 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