Sacha Tomey

Sacha Tomey's Blog

Automated SQL Server 2000 Data Dictionary

 
The two stored procedures below will help with automating the generation and maintenance of a database data dictionary.
 
If you provide a description for each database field in a SQL Database, these stored procedures can be used to extract that description information together with some other useful column details.
 
The first stored proc simply details the column information for a single table:
 
CREATE PROCEDURE uxp_Table_Data_Dictionary
    @Table_Name varchar(50)
AS
-- Extract the table data dictionary
SELECT

    TABLE_NAME,
    ORDINAL_POSITION, 
    COLUMN_NAME, 
    CASE DATA_TYPE 
        WHEN 'varchar' THEN 'varchar('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20))+')'
        ELSE DATA_TYPE
    END AS DATA_TYPE,
    ISNULL(COLUMN_DEFAULT, '') AS COLUMN_DEFAULT,
    IS_NULLABLE,
    value AS PROPERTY_VALUE

FROM
    INFORMATION_SCHEMA.COLUMNS AS info 
        JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @Table_Name, 'column', default) AS extended ON info.COLUMN_NAME = extended.objname

WHERE

    TABLE_NAME = @Table_Name
The second stored procedure extends this a little and reports the column information for all user tables within the current database:
 
CREATE PROCEDURE uxp_System_Data_Dictionary
AS
    -- Create a cursor containing all user tables
    DECLARE @tableName varchar(40) 
    DECLARE table_cursor CURSOR FOR
    SELECT name FROM sysobjects WHERE type = 'U' AND name <> 'dtproperties'
    OPEN table_cursor
 
    -- Perform the first fetch.
    FETCH NEXT FROM table_cursor INTO @tableName
 
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Execute the table data dictionary generator
        EXEC uxp_Table_Data_Dictionary @tableName
 
        -- Fetch the next table
        FETCH NEXT FROM table_cursor INTO @tableName
    END
 
    -- Tidy Up
    CLOSE table_cursor
    DEALLOCATE table_cursor

PartialCachingControl.CachedControl confusion

 
If you are using the @ OutputCache directive on a user control that you are adding programatically you will not receive a reference to the control type directly when you 'LoadControl'.  Instead you will be returned an object of type PartialCachingControl.
 
The CachedControl property is not hugely intuitive, it's actually set when the object is cached the first time - it's the property saying, "By the way, I've cached this object for you, just so you know".  If the object is already in the cache the CachedControl property returns null, this time it's saying, "I haven't had to cache anything as it was already in the cache".
 
If you do not add the PartialCachingControl to the control tree the CachedControl property will ALWAYS return null.
 
Here is a simple routine that demonstrates this:
 

void Page_Init()
{

Control Item;

Item = Page.LoadControl("test1.ascx");

Test1a.Controls.Add(Item);  // Comment this line to see the "always null" behavior.

if ( ((PartialCachingControl)Item).CachedControl != null)
{
      Response.Write("CachedControl is not null! - Control was not in the cache but is now ");

}
else
{
     Response.Write("CachedControl is null! - Control was already in the cache");

}

 

// To obtain a reference to your object access the control collection (Alternatively you can use 'FindControl')

MyUserControl myControl = (MyUserControl) Item.Controls[0];

}