Explore Database

/*
|================================================================================|
| 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
    )