Do a good job for your database data dictionary

Database design, maintenance is as long as the problem, often to query data and view the original field defined what was meant. Use sp_help to look at it? Slow to have to recall the original intention of the half-day design. Start, as long as the design work to spare, you can very easy access to the original design of the database. First, we create a data table, give the table and each field with Chinese comments. As follows:

  1. CREATE TABLE Employee (
  2. EmpIDintNOT NULL, – employee number
  3. EmpName varchar (32), – the name of the employee
  4. EmpPhone varchar (32) – Employee phone
  5. EmpStateint– Employee Status [1: on-the-job 2: left]
  6. )
  7. ;
  9. ALTER TABLE Employee ADD CONSTRAINT PK_Employee
  10. Primary KEY clustered (empid)
  11. ;
  13. To EXEC sp_addextendedproperty‘MS_Description’, ‘staff table’, ‘User’, dbo, ‘table’, Employee
  14. ;
  15. EXEC sp_addextendedproperty‘MS_Description’, ‘Employee Number’, ‘User’, dbo, ‘table’ Employee, ‘column’, empid
  16. ;
  18. EXEC sp_addextendedproperty‘MS_Description’, ‘the staff Name’, ‘user’, dbo, ‘table’ Employee, ‘column’, EmpName
  19. ;
  21. The EXEC sp_addextendedproperty‘MS_Description’, ’employee telephone’, ‘User’, dbo, ‘table’ Employee, ‘column’, EmpPhone
  22. ;
  24. EXEC sp_addextendedproperty‘MS_Description’, ’employee status [1: on-the-job, 2: Separation]’, ‘User’, dbo, ‘table’ Employee, ‘column’, EmpState
  25. ;

Then, create a stored procedure, as follows:

  1. Create procedure DataDictionary_GetbyTableName
  2. @ TableName varchar (32)
  3. as
  4. select b. [name] Tablename, Isnull (c. [name], ‘Table’) ColName, convert (varchar (256), a. [value]) Description
  5. from sys.extended_properties a inner join sysobjects b on b.id = a.major_id
  6. left join syscolumns c on a.minor_id = c.colid and a.major_id = c.id
  7. where b. [name] = @ TableName

Finally, the need to view a table, execute the stored procedure + table name. On it. As follows:

  1. DataDictionary_GetbyTableName employee

Get is:

Did not feel convenient.

Posted by databasesql