EXEC sys.sp_addextendedproperty
  @name=N'ColumnDescription',
  @value=N'Credit Card Expiration Date' ,
  @level0type=N'SCHEMA',
  @level0name=N'dbo',
  @level1type=N'TABLE',
  @level1name=N'CustomerOrder',
  @level2type=N'COLUMN',
  @level2name=N'Order_CCExp'

CREATE procedure [dbo].[sp_find]         
@findcolumn varchar(50)         
as         
begin         
 set nocount on         
 select sysobjects.name as TableFound,syscolumns.name as ColumnFound        
 from sysobjects        
   inner join syscolumns on sysobjects.id=syscolumns.id        
   where sysobjects.xtype='U'     
 and (syscolumns.name like '%' + @findcolumn +'%'       
   or sysobjects.name like '%' + @findcolumn +'%' )     
order by   TableFound,ColumnFound        
end   
GO
EXEC sys.sp_addextendedproperty  
         @name = N'Version',
         @value = N'9.0.154.90',  
         @level0type = N'SCHEMA',  @level0name = 'dbo',  
         @level1type = N'PROCEDURE', @level1name = 'sp_find';
EXEC sys.sp_addextendedproperty  
         @name = N'Purpose',
         @value = N'simple tool to find column or table names that are LIKE the inputed value',  
         @level0type = N'SCHEMA',  @level0name = 'dbo',  
         @level1type = N'PROCEDURE', @level1name = 'sp_find';
--show all extended properties
SELECT objtype, objname, name, value  
 FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);    
--get just the "Version" that i created:
SELECT objtype, objname, name, value  
 FROM fn_listextendedproperty ('Version', 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);

EXEC sys.sp_addextendedproperty  
@name = N'Version',
@value = N'9.0.154.90'

EXEC sys.sp_addextendedproperty  
@name = N'InstanceName',
@value = N'LOWELL\SQL2008'

<#  
 =====================
 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"

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|'
GO

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