In the previous posting we discussed database objects and how to query for a list of objects. In this posting we will discuss how to query the schema of an object. What is a schema? A schema defines the database object structure, for example, the structure of the tables, such as, fields, data types, keys, constraints, etc.

The following SQL statement defines the user table structure:

{TABLE_NAME, COLUMN NAME, DATA TYPE, IS NULL, PRIMARY KEY, COLUMN DESCRIPTION}

   1:  USE Northwind
   2:   
   3:  DECLARE  @pkeys TABLE 
   4:  (  
   5:      t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,  
   6:      cn SYSNAME, ks INT, pn SYSNAME  
   7:  )
   8:  INSERT into @pkeys EXEC  sys.sp_pkeys 'Orders'                              
   9:  SELECT 
  10:      a.TABLE_NAME,
  11:      COLUMN_NAME, 
  12:      [DATA TYPE] = UPPER(DATA_TYPE)+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') 
  13:                  THEN  
  14:                      '(' + CAST(NUMERIC_PRECISION AS VARCHAR)+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'  
  15:                  ELSE 
  16:                      '' 
  17:                  END  
  18:                  + CASE COLUMNPROPERTY(OBJECT_ID(a.TABLE_NAME), COLUMN_NAME, 'IsIdentity')  
  19:                  WHEN 1 
  20:                  THEN  
  21:                      ' IDENTITY (' + CAST(IDENT_SEED(a.TABLE_NAME) AS VARCHAR(32)) + 
  22:                      ', ' + CAST(IDENT_INCR(a.TABLE_NAME) AS VARCHAR(32)) + ')' 
  23:                  ELSE 
  24:                      '' 
  25:                  END  
  26:                  + CASE RIGHT(DATA_TYPE, 4)
  27:                  WHEN 'CHAR' 
  28:                  THEN  
  29:                      ' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' 
  30:                  ELSE 
  31:                      '' 
  32:                  END ,  
  33:      [IS NULL] = CASE a.IS_NULLABLE 
  34:                  WHEN 
  35:                      'No'
  36:                  THEN 
  37:                      ' NOT ' 
  38:                  ELSE 
  39:                      ' ' 
  40:                  END  
  41:                      + 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,  
  42:                      2, LEN(COLUMN_DEFAULT)-2), '' ),
  43:      [PRIMARY KEY] =CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE ' ' END ,
  44:      [COLUMN DESCRIPTION] = ex.value
  45:  FROM  
  46:      INFORMATION_SCHEMA.COLUMNS a 
  47:      LEFT OUTER JOIN
  48:          @pkeys pk
  49:      ON
  50:          pk.cn = a.COLUMN_NAME
  51:      LEFT OUTER JOIN  
  52:          sys.columns c 
  53:      ON
  54:          a.TABLE_NAME = OBJECT_NAME(c.object_id) 
  55:      AND a.COLUMN_NAME = c.name 
  56:      AND a.TABLE_NAME <> 'sysdiagrams '
  57:      LEFT OUTER JOIN  
  58:          sys.extended_properties ex  
  59:      ON  
  60:          ex.major_id = c.object_id 
  61:      AND ex.minor_id = c.column_id  
  62:      AND ex.name = 'MS_Description'   
  63:      WHERE  
  64:          a.TABLE_NAME ='Orders'

Adding/Updating Column Descriptions

The system stored procedure sp_addextendedproperty adds a new extended property to a database object. The procedure sp_updateextendedproperty updates the value of an existing extended property.

Consider the following query:

   1:  USE Northwind
   2:  DECLARE @temp as sql_variant
   3:  SET @temp = null
   4:  SELECT @temp = value
   5:  FROM   ::fn_listextendedproperty ('MS_Description','user','dbo','table', 'Orders' ,'column','OrderID')
   6:  IF (@temp is null)
   7:  BEGIN
   8:      EXEC sp_addextendedproperty  'MS_Description','a new description for column OrderID', 'user', dbo, 'table',  'Orders' , 'column', 'OrderID'
   9:  END
  10:  ELSE
  11:      EXEC sp_updateextendedproperty  'MS_Description','new description for column OrderID', 'user', dbo, 'table',  'Orders' , 'column', 'OrderID'

The following table outlines the result of the above query:

TABLE_NAME COLUMN_NAME DATA TYPE IS NULL PRIMARY KEY COLUMN DESCRIPTION
Orders OrderID INT IDENTITY (1, 1) NOT NULL Yes a new description for column OrderID
Orders CustomerID NCHAR (5) NULL   NULL
Orders EmployeeID INT NULL   NULL
Orders OrderDate DATETIME NULL   NULL
Orders RequiredDate DATETIME NULL   NULL
Orders ShippedDate DATETIME NULL   NULL
Orders ShipVia INT NULL   NULL
Orders Freight MONEY NULL DEFAULT 0   NULL
Orders ShipName NVARCHAR (40) NULL   NULL
Orders ShipAddress NVARCHAR (60) NULL   NULL
Orders ShipCity NVARCHAR (15) NULL   NULL
Orders ShipRegion NVARCHAR (15) NULL   NULL
Orders ShipPostalCode NVARCHAR (10) NULL   NULL
Orders ShipCountry NVARCHAR (15) NULL   NULL