INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Explore Database by LNBruno
Posted: 15 May 07

Whenever I'm confronted with a new database, I use the following to get pertinent info about it.  

It includes SQL for an initial look at the db objects, then several for tables, views, SPs, and saved diagrams (if any) that I've collected over...a while.  It's not all-inclusive and there may be some newer ways to get at some of this, but I still find myself cracking it open fairly frequently.

If nothing else, it could save you a few keystrokes...

CODE

/*
|================================================================================|
| INITIAL EXPLORATION OF DATABASE OBJECTS                                        |
|================================================================================|
*/

SELECT o.xtype ObjectType
   , CASE o.xtype
        WHEN 'C'  THEN 'CHECK constraint'
        WHEN 'D'  THEN 'Default or DEFAULT constraint'
        WHEN 'F'  THEN 'FOREIGN KEY constraint'
        WHEN 'L'  THEN '[Log]'
        WHEN 'FN' THEN 'Scalar function'
        WHEN 'IF' THEN 'Inlined table-function'
        WHEN 'P'  THEN 'Stored procedure'
        WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
        WHEN 'RF' THEN 'Replication filter stored procedure'
        WHEN 'S'  THEN 'System table'
        WHEN 'TF' THEN 'Table function'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'U'  THEN 'User table'
        WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
        WHEN 'V'  THEN 'View'
        WHEN 'X'  THEN 'Extended stored procedure'
   END ObjectTypeDesc
   , o.name AS table_view_name
   , c.name AS column_name
   , c.xtype AS column_type
   , ct.name AS column_type_name
   , c.length AS column_length
   , c.xprec AS column_precision
   , c.xscale AS column_scale
   , CASE
        WHEN c.ISNULLABLE = 1 THEN 'Nulls Allowed'
        ELSE 'Non-Null'
     END AS null_values
FROM sysobjects o
INNER JOIN syscolumns c
    ON o.id = c.id
INNER JOIN systypes ct
    ON c.xtype = ct.xtype
ORDER BY o.name, c.colid

/*
|================================================================================|
| EXPLORE TABLES                                                                 |
|================================================================================|
*/
--list ALL tables:
SELECT * FROM INFORMATION_SCHEMA.tables

--list ALL tables and associated data from a database:
SELECT * FROM information_schema.columns
WHERE TABLE_CATALOG = 'YourDatabaseName'
ORDER BY table_name, ordinal_position

--to get tablename, object name, type, and object type:
SELECT ISNULL(B.Name, '') AS TableName
    , A.Name AS ObjectName
    , A.XType
    , X.ObjectType
FROM sysobjects A
LEFT OUTER JOIN sysobjects B
    ON A.Parent_Obj = B.ID OR A.Parent_Obj = NULL
INNER JOIN
    (
    SELECT 1 AS Pos, 'U' AS XType, 'User table' AS ObjectType UNION
    SELECT 2, 'D', 'DEFAULT Value' UNION
    SELECT 3, 'PK', 'PRIMARY KEY constraint' UNION
    SELECT 4, 'UQ', 'UNIQUE constraint' UNION
    SELECT 5, 'F', 'FOREIGN KEY constraint' UNION
    SELECT 6, 'C', 'CHECK constraint' UNION
    SELECT 7, 'TR', 'Trigger' UNION
    SELECT 8, 'P', 'Stored Procedure'
    ) X
    ON X.XType = A.XType
WHERE ('U' IN (A.XType, B.XType) OR X.XType = 'P')
    AND A.Name NOT LIKE 'dt%'
    AND (B.Name IS NULL OR B.Name NOT LIKE 'dt%')
ORDER BY ISNULL(B.Name, A.Name), X.pos

--list User Tables:
SELECT TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME <> 'dtproperties'
ORDER BY TableName

--list ALL columns for a specified table:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'YourTableName'

--list identity column for tables:
SELECT o.name AS [Table Name]
   , c.name AS [Column Name]
FROM sysobjects o
JOIN syscolumns c
    ON o.id = c.id
WHERE o.type = 'U'
    AND COLUMNPROPERTY(o.id, c.name, 'IsIdentity') = 1
ORDER BY 1

--list indexes for all tables:
SELECT OBJECT_NAME(i.id) AS TableName
    , i.name AS IndexName
    , CASE INDEXPROPERTY(i.id, i.name, 'IsClustered')
          WHEN 1 THEN 'Yes'
          ELSE 'No'
      END AS IsClustered
    , CASE INDEXPROPERTY(i.id, i.name, 'IsUnique')
          WHEN 1 THEN 'Yes'
          ELSE 'No'
      END AS IsUnique
    , STATS_DATE(i.id, i.indid) AS LastUpdatedDate
    , i.id AS ObjectID
    , i.indid AS IndexID
FROM sysindexes AS i
WHERE 1 NOT IN (INDEXPROPERTY(i.id, i.name, 'IsStatistics')
    , INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics')
    , INDEXPROPERTY(i.id, i.name, 'IsHypothetical'))
    AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
     --to filter out the text/ntext/image columns in sysindexes:
    AND INDEXPROPERTY(i.id, i.name, 'IndexDepth') > 0
ORDER BY TableName, IndexID

/*
|================================================================================|
| EXPLORE VIEWS                                                                  |
|================================================================================|
*/
-- list User Views:
SELECT TABLE_NAME AS UserView
FROM information_schema.views
WHERE TABLE_NAME NOT IN ('sysconstraints', 'syssegments')
ORDER BY UserView

/*
|================================================================================|
| EXPLORE STORED PROCEDURES                                                      |
|================================================================================|
*/
--list User Stored Procedures:
SELECT Routine_Name AS SPname
FROM Information_Schema.Routines
WHERE Routine_Type = 'Procedure'
    AND Routine_Name NOT LIKE 'dt[_]%'
ORDER BY SPname

--list details about a specified stored procedure:
sp_help usp_CalcInterest

--list actual contents (text) of a specified stored procedure:
sp_helptext usp_CalcInterest

/*
|================================================================================|
| EXPLORE DIAGRAM DATA                                                           |
|================================================================================|
*/
--list all data for saved diagrams:
SELECT *
FROM dtproperties d1
WHERE EXISTS
    (
    SELECT *
    FROM dtproperties d2
    WHERE d2.property = 'DtgSchemaOBJECT'
        AND d1.objectid = d2.objectid
    )

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close