Gerard's codebase: List objects in a SQL database

Gerard's codebase

This is a personal repository of code snippets and information.

Over the years I have generated lots of little sub programs and stored away acres of useful code snippets. The problem is always to locate them.

Even more time wasting is forgetting how to do simple things when you havnt developed in an environment for a few years (or even a few months)

My new years resolution is to start putting them up in a common place as I produce them. (thanks google)

They are handy for me and, with a bit of a clean up and documentation, they might be handy for others if they wander in here.

Gerard 2008

Tuesday, September 30, 2008

List objects in a SQL database

This base script for querying SQL Servers system tables is the sharpest tool in the shed.
I mainly use it as the best way to search for occurance of fields in tables but can be modified in a multitude of ways. The sysobjects.xtype field is the key and I have listed its values below
pt.sql

-- C : CHECK constraint
-- D : Default or DEFAULT constraint
-- F : FOREIGN KEY constraint
-- L : Log
-- P : Stored procedure
-- PK : PRIMARY KEY constraint (type is K)
-- RF : Replication filter stored procedure
-- S : System tables
-- TR : Triggers
-- U : User table
-- UQ : UNIQUE constraint (type is K)
-- V : Views
-- X : Extended stored procedure
-- TF : Functions
USE master -- only an example - run local to the database you are searching
SELECT
 o.name AS TableName,
 o.type AS ObjectType,
 c.name AS FieldName,
 s.name AS FieldType,
 c.prec,
 c.scale,
 c.isnullable
FROM
 sysobjects AS o
   INNER JOIN syscolumns AS c
    ON c.id = o.id
       INNER JOIN systypes AS s
       ON c.xtype = s.xtype
WHERE
--select all user tables
o.type = 'U'
--other example select all user tables containing the field SalesPersonId
--o.type = 'U' and c.name = 'SalesPersonId'

Gerard's codebase