Default extended properties

• jasonclements32304

  Ten Centuries

  Points: 1122

  More actions

  January 16, 2013 at 9:10 am

  #267109

  Me and my team are using Red-gate's SQLDOC for our DB Docs. We have a built nightly PowerShell that run SQLDOC command line that is running to over the current post SQLDOCs.

  <#

  =====================

  Delete Current SQLDOC

  =====================

  #>

  $path = '\\server\d$\PVP\PVP21\webcontent\Powershell\Database_Documentation\DBPrime1\'

  Get-ChildItem $path |

  Sort-Object { $_.Name -as [Version] } |

  Select-Object -Last 1 |

  Remove-Item -recurse

  <#

  ===============

  Create SQLDOC

  ===============

  #>

  $CMD = 'C:\Program Files (x86)\Red Gate\SQL Doc 2\sqldoc.exe'

  $arg1 = '/project:H:\My Documents\SQL Server Management Studio\Red-Gate\DBPrime1.sqldoc'

  $arg2 = '/filetype:html-frames'

  $arg3 = '/force'

  $arg4 = '/outputfolder:"\\server\d$\PVP\PVP21\webcontent\Powershell\Database_Documentation\DBPrime1"'

  & $CMD $arg1 $arg2 $arg3 $arg4

  <#

  "C:\Program Files (x86)\Red Gate\SQL Doc 2\sqldoc.exe" /project:"H:\My Documents\SQL Server Management Studio\Red-Gate\DBPrime1.sqldoc" /filetype:html-frames /force /outputfolder:"\\rpt102\d$\PVP\PVP21\webcontent\Powershell\Database_Documentation\DBPrime1"

  #>

  <#

  ===============

  Rename Directory

  ===============

  #>

  cd \\server\d$\PVP\PVP21\webcontent\Powershell\Database_Documentation\DBPrime1

  dir | Where-Object {$_.Name} | Rename-Item -NewName "DBPrime1"

  I also created a SSMSToolPack Custom Script. That we can run to create our custom extended properties when we are developing the tables, views, SP, and etc.

  DECLARE @type varchar(50), @timestamp DATETIME, @user VARCHAR(100)

  SELECT @type=CASE WHEN [type] = 'U'

  THEN 'TABLE'

  WHEN [type] = 'V'

  THEN'VIEW'

  WHEN [type] = 'P'

  THEN 'PROCEDURE

  WHEN [type] = 'PK'

  THEN 'PRIMARY_KEY_CONSTRAINT'

  WHEN [TYPE] = 'D'

  THEN 'DEFAULT_CONSTRAINT'

  WHEN [type] = 'FN'

  THEN 'SQL_SCALAR_FUNCTION'

  END

  FROM sys.objects

  WHERE [NAME] = '|ObjectName|'

  SELECT @timestamp=GETDATE()

  SELECT @user=SYSTEM_USER

  EXEC sys.sp_addextendedproperty @name=N'Created by', @value=@user , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@type,@level1name=N'|ObjectName|'

  EXEC sys.sp_addextendedproperty @name=N'Purpose', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@type,@level1name=N'|ObjectName|'

  EXEC sys.sp_addextendedproperty @name=N'Created on', @value=@timestamp, @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@type,@level1name=N'|ObjectName|'

  My new thought was this morning is it even possible to have default extended properties when the tables or etc. are created? Default extended properties. Standards for all users to fill in.

  I think DDL Trigger in SQL-Server is my answer.

• jasonclements32304

  Ten Centuries

  Points: 1122

  More actions

  January 16, 2013 at 1:57 pm

  #1578025

  Answered my own question today.

  I setup a new Database trigger:

  CREATE TRIGGER [DB_Add_extended_propeties]

  ON DATABASE

  FOR CREATE_TABLE,CREATE_VIEW, CREATE_PROCEDURE

  AS

  SET NOCOUNT ON

  DECLARE @eventInfo XML

  SET @eventInfo = EVENTDATA()

  DECLARE @timestamp DATETIME, @user VARCHAR(100), @ObjectType VARCHAR(50), @ObjectName VARCHAR(50)

  SELECT @timestamp=GETDATE()

  SELECT @user=SYSTEM_USER

  SET @ObjectType=CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/ObjectType)'))

  SET @ObjectName=CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/ObjectName)'))

  EXEC sys.sp_addextendedproperty @name=N'Created by', @value=@user , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@ObjectType ,@level1name=@ObjectName

  EXEC sys.sp_addextendedproperty @name=N'Purpose', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@ObjectType ,@level1name=@ObjectName

  EXEC sys.sp_addextendedproperty @name=N'Created on', @value=@timestamp, @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@ObjectType ,@level1name=@ObjectName

  GO