How to extract database schema information?
search cancel

How to extract database schema information?


Article ID: 180005


Updated On:


IT Management Suite


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?


The following information is provided just as a reference since we don't provide detailed information regarding our database schema. How exactly this information can be obtained?, there are many examples online that can be used to get such details:

 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

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

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')


 Method 2:

Using SQL Server Management Studio

  1. Open SQL Server Management Studio.
  2. Right-click the database that contains the desired data (usually called "Symantec_CMDB") and select Tasks - Generate Scripts...

  3. A wizard window will appear. Click next.

  4. On the next screen select "Select specific database objects". Then expand the "Tables" section and select the desired tables individually or "Select All":

  5. Click Next and you'll arrive at the screen below. Specify the desired file name for the script. In most cases "Save as script file>Single script file". Specify the path and file name as needed. Then click the "Advanced" button.

  6. Find on the list the section called "Types of data to script" and click the down arrow as shown below and make sure "Schema only" is selected (here is where you can choose to script the schema (table itself), the data, or both). Click OK, and then Next:

  7. You will arrive at the summary screen below.

  8. Click Next and the scripts will be generated automatically.


  9. 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.