List tables, columns and defaults in a database

The code snippet lists all tables, columns, data type, null-ability and default values for a database. Tested on MS SQL Server 2005. Handy for a quick check of data types and defaults.

/* List tables, columns, data type and default values */
USE ASI2353;
		,[COLUMN_NAME] AS "Column"
		,[DATA_TYPE] AS "Data Type"
		,[COLUMN_DEFAULT] AS "Default"
		,[IS_NULLABLE] AS "Nullable"
	FROM information_schema.columns
WHERE [TABLE_NAME] != 'sysdiagrams'

The last table

Well, not really. Within a Data Pump SQL project it is called [dbo].[Last] and is primarily intended to be queried by third-party applications, like SCADA, EP, CEP or reporting tools. The table contains only one row with the latest serial number and the time-stamp from the [dbo].[PartData] table. Here is a typical example:

ID Val TimeUTC TimeLoc
1 200805151007 2008-05-15 14:30:01 2008-05-15 10:30:01


The table structure may vary, depending on the project. Time column in UTC allows for applications outside of the time zone.

Column Name Data Type Description
ID int ID column for this table; always 1 (one row only).
Val bigint Serial number of the last entry from [dbo].[PartData].
TimeUTC datetime Time-stamp of the last entry in UTC (GMT).
TimeLoc datetime Optional. Local time of the last entry.


The “third-party” application should query the [dbo].[Last] table and determine if there were any changes to the [dbo].[PartData] table since the last query.

SELECT [Val], [TimeLoc] 
    FROM [dbo].[Last] WHERE ID = 1;

Once a change is detected, it is OK to query the [dbo].[PartData] table.

/* Time of the last query */
DECLARE @LastTime datetime;
SET @LastTime = '2008-05-15 14:30:01';
/* Query the table */
    FROM [dbo].[PartData]
WHERE [RecordDate]  >= @LastTime;

Standard tables

Here is the list of tables in a typical Data Pump SQL project:

Object Name Schema Type Description
ClientPrivate dbo TABLE Client (Excel) setup info
Error dbo TABLE Error codes & descriptions
Field dbo TABLE Fields collected in the dbo.PartData
Header dbo TABLE Collection of header fields and order in reports
Limit dbo TABLE Process limits; LSL, USL
PartData dbo TABLE One row for each part
Station dbo TABLE Station names and IDs
Style dbo TABLE Style names and IDs
Last dbo TABLE Serial number and time-stamp of the last entry to the PartData table


For more details see the knowledge database article.

Basic DB maintenance

The code is useful for machine installations of  Data Pump SQL projects which periodically clear the  [dbo].[PartData] table. Before using this, make sure you first check the database as described here.  Change the ‘ASI2353’ to the name of your database.

/* Shrink database, leave file size; just pack inside the file
   Initial file sizes: DB: 2048MB; Log:256MB */
DBCC ShrinkDatabase ('ASI2353', NOTRUNCATE);
/* Defrag Indexes */
DBCC IndexDefrag ('ASI2353', 'PartData', 'pk_Part');
/* Reclaim space from dropped chars, varchars text.. */
DBCC CleanTable ('ASI2353', 'PartData');

Listing tables in a DB

The code snippet lists all the tables in a Data Pump SQL project and all column names, data types and null-ability for a specific table. Useful when using a reporting tool to dynamically query the database or table structure.

/* Define the DB to use */
USE ASI2353;
/* List all tables in a DB */
SELECT '[' + [TABLE_SCHEMA] + '].[' + [TABLE_NAME] + ']'
    FROM information_schema.tables
/* List columns for a specified table (dbo.PartData) */
    FROM information_schema.columns

Find by engraved number

To find a part by the engraved number for a Data Pump SQL series project use:

-- Find part by the engraved number
USE ASI2353;
    @RC int,
    @LookFor bigint;
-- Get serial number for the engraved part
SELECT @LookFor = [SerialNumber]
    FROM [dbo].[PartData]
    WHERE [EngraveAs] = '1791010083'; -- the engraved number
-- Now get data based on the serial number
EXECUTE @RC = [dbo].[p_FindBySerial] @LookFor;

If the “part run” was in rework mode and tracking number was correctly entered, multiple records are returned. This should be used from reporting tool; if using Data Pump Client program, simply click on Find Part.

Find by serial number

To find a part by serial number (from a reject label) for a Data Pump SQL series project  use:

-- Find part by the serial number
USE ASI2353;
    @RC int,
    @LookFor bigint;
EXECUTE @RC = [dbo].[p_FindBySerial] 
   @LookFor = 200801101791; -- the serial number 

If the “part run” was in rework mode and tracking number was correctly entered,  multiple records are returned.

Serial Numbers

There are typically two systems for assigning serial numbers to parts on a line.

  1. The serial number is assigned at the beginning of the line (process) and usually engraved at that point. In a case of rework (reprocessing) the same serial number is scanned as the part re-enters the process.
  2. In the second case, only good parts are engraved just before unloading, and all other are issued a “sticker” with a serial number. If a part re-enters the line, a new number is issued and the previous one is scanned.

In the second case it is a bit harder to find (merge) all data for a part which re-entered the line several times. The algorithm for it is similar to classic family tree navigation. To simplify the explanation, create the following table with some data and look at the result of the select statement.

    SSN BIGINT PRIMARY KEY,  -- social security number
    ParentSSN BIGINT,        -- parent's social security No.
    [Name] varchar(25)       -- person's name
/* Insert some data */
INSERT INTO #People (SSN, ParentSSN, [Name])
		VALUES (1199, 700, 'Doug');
INSERT INTO #People (SSN, ParentSSN, [Name])
		VALUES (1105, 1101, 'Sandy');
INSERT INTO #People (SSN, ParentSSN, [Name])
		VALUES (1234, 1100, 'Tamara');
INSERT INTO #People (SSN, ParentSSN, [Name])
		VALUES (1001, 600, 'Don');
INSERT INTO #People (SSN, ParentSSN, [Name])
		VALUES (1235, 1177, 'John');
INSERT INTO #People (SSN, ParentSSN, [Name])
		VALUES (1100, 1001, 'Deb');
INSERT INTO #People (SSN, ParentSSN, [Name])
		VALUES (1272, 995, 'Sean');

Some of these people are related, some not. To find all ancestors of a person, use the following:

NM varchar(25),        -- person's name 
SN bigint primary key, -- person SSN
PA bigint,             -- parent SSN 
GE int                 -- generation
    @Gen int,
    @LookFor bigint;
/* The person's SSN to start with */
SET @LookFor = 1234;
/* Insert the first person, generation 0 */
SET @Gen = 0;
    SELECT [SSN], [ParentSSN], @Gen, [Name]
        From #People
    WHERE [SSN] = @LookFor;
WHILE @@RowCount  > 0
        SET @Gen = @Gen + 1;
        INSERT INTO #tmp(SN, PA, GE, NM)
            SELECT [SSN], [ParentSSN], @Gen, [Name]
                FROM #People
                    JOIN #tmp
                        ON #tmp.[GE] = @Gen - 1
                        AND #tmp.[PA] = [SSN]
    [NM] AS "Name",
    [SN] AS "SSN",
    [PA] AS "Parent SSN",
    [GE] AS "Generation"

So what does this have to do with car parts (pumps)?

The search algorithm is the same, substitute:

  • SSN = Serial Number,
  • ParentSSN = Tracking Number (previous Serial Number),
  • ignore the name field

and the same code will find all serial numbers associated with a reworked pump.