Voici une procédure stockée que j’ai écrite afin de documenter facilement une base de données. Elle retourne l’ensemble des vues et des tables de la base de données, de même que le détail des colonnes de chacun des objets.[sourcecode language=’sql’]/*
=============================================
Author: Benjamin Olivier
Create date: 2009-02-02
Description: Permet de créer un dictionnaire pour la BD spécifiée.
Ex:
getDictionary ‘DBA’
=============================================
*/
CREATE PROCEDURE getDictionary
@Database VARCHAR(128) = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @Database IS NULL
SET @Database = db_name()
IF object_id(N’tempdb.dbo.#Definitions’) IS NOT NULL
DROP TABLE #Definitions
CREATE TABLE #Definitions
(
[Database] [nvarchar](128) NULL,
[ObjectType] [varchar](10) NOT NULL,
[Schema] [nvarchar](128) NULL,
[Table] [sysname] NULL,
[Column] [sysname] NULL,
[ColumnDefault] [nvarchar](4000) NULL,
[IsNullable] [varchar](3) NULL,
[DataType] [nvarchar](128) NULL,
[Length] [varchar](64) NULL,
[KeyType] varchar(2) NULL,
[Description] [sql_variant] NULL
)
–Database
INSERT INTO #Definitions([Database], ObjectType)
SELECT @Database, ‘Database’
EXEC(‘UPDATE d
SET d.Description = x.value
FROM #Definitions d,
‘ + @Database + ‘.sys.fn_listextendedproperty(default, default, default, default, default, default, default) x
WHERE (d.[Database] = ”’ + @Database + ”’)’)
–TABLE & VIEW
EXEC(‘INSERT INTO #Definitions([Database], [ObjectType], [Schema], [Table], [Description])
SELECT t.TABLE_CATALOG,
t.TABLE_TYPE,
t.TABLE_SCHEMA,
t.TABLE_NAME,
ISNULL(x.value, ””) Description
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES t
OUTER APPLY ‘ + @Database + ‘.sys.fn_listextendedproperty (NULL, ”schema”, t.TABLE_SCHEMA, ”TABLE”, t.TABLE_NAME, NULL, NULL) x’)
–COLUMN
EXEC(‘INSERT INTO #Definitions([Database], [ObjectType], [Schema], [Table], [Column], [ColumnDefault], IsNullable, [DataType], Length, [Description])
SELECT c.TABLE_CATALOG [Database],
”Column” ObjectType,
c.TABLE_SCHEMA [Schema],
c.TABLE_NAME [Table],
c.COLUMN_NAME [Column],
c.COLUMN_DEFAULT,
c.IS_NULLABLE IsNullable,
c.DATA_TYPE DataType,
CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)
WHEN c.NUMERIC_PRECISION IS NOT NULL THEN ”(” + CAST(c.NUMERIC_PRECISION AS VARCHAR) + ”, ” + CAST(c.NUMERIC_SCALE AS VARCHAR) + ”)”
ELSE CAST(c.DATETIME_PRECISION AS VARCHAR)
END Length,
COALESCE(xt.Value, xv.Value)
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.COLUMNS c
OUTER APPLY ‘ + @Database + ‘.sys.fn_listextendedproperty (NULL, ”schema”, c.TABLE_SCHEMA, ”table”, c.TABLE_NAME, ”column”, c.COLUMN_NAME) xT
OUTER APPLY ‘ + @Database + ‘.sys.fn_listextendedproperty (NULL, ”schema”, c.TABLE_SCHEMA, ”view”, c.TABLE_NAME, ”column”, c.COLUMN_NAME) xV
‘)
EXEC(‘UPDATE d
SET KeyType = o.xtype
FROM #Definitions d
INNER JOIN ‘ + @Database + ‘.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U ON d.[Database] = U.TABLE_CATALOG AND d.[SCHEMA] = U.TABLE_SCHEMA AND d.[Table] = U.TABLE_NAME AND d.[COLUMN] = U.COLUMN_NAME
INNER JOIN ‘ + @Database + ‘.sys.sysobjects O ON U.CONSTRAINT_NAME = O.name
WHERE O.xtype in (”F”,”PK”)’)
SELECT [Database]
,ObjectType
,ISNULL([Schema], ”) [Schema]
,ISNULL([Table], ”) [Table]
,ISNULL([Column], ”) [Column]
,ISNULL(ColumnDefault, ”) [ColumnDefault]
,ISNULL(IsNullable, ”) [IsNullable]
,ISNULL(DataType, ”) [DataType]
,ISNULL(Length, ”) [Length]
,ISNULL(KeyType, ”) [KeyType]
,ISNULL(Description, ”) [Description]
FROM #Definitions
ORDER BY 1,3,4,5
END
GO[/sourcecode]
Une fois la sproc créée, il suffit de l’appeler avec la commande suivante :
[sourcecode language=’sql’]EXEC getDictionary ‘AdventureWorks2008′[/sourcecode]
Il est ensuite pratique d’exporter les résultats dans un fichier Excel. La colonne description est particulièrement intéressante et provient des commentaires que vous pouvez inscrire pour un champ, une vue, une table via votre utilitaire d’administration préféré ; voici, par exemple ou inscrire le détail en utilisant SSMS :
Il est également possible de lancer la commande suivante pour inscrire une description :
[sourcecode language=’sql’]EXEC sys.sp_addextendedproperty @name=N’MS_Description’,
@value=N’Description’ ,
@level0type=N’SCHEMA’,@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’MaTable’,
@level2type=N’COLUMN’,@level2name=N’MaColonne’
[/sourcecode]
Note : Cette sproc ne fonctionnera pas sur SQL Server 2000.