Thanks for all the great replies. I'm learning alot.
I was working on reviewing extended properties we currently have and forgot to join to sys.objects. I was joined to sys.table. So I thought I was looking at orphans. Brain Fart!
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 have created a have 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|'
GO
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.