May 9, 2008 at 4:22 am
Hi everybody, misters
I create extended properties asociated to columns of tables:
EXEC sp_addextendedproperty
@name = N'TablaMaestra', @value = 'Descripcion',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table', @level1name = tabla1,
@level2type = N'Column', @level2name = Descripcion;
GO
I get the value of extended property of columns of the "tabla1" table:
select objtype,objname,[name],[value]
from fn_listextendedproperty (null,'Schema','dbo','TABLE','tabla1','COLUMN',null)
How can I get extended properties of all columns of all tables ?
Greetings, regards, thanks in advance
November 24, 2009 at 6:40 am
[font="Courier New"]SELECT SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
clmns.name AS [Column_Name],
p.name AS [Name],
CAST(p.value AS SQL_VARIANT) AS [Value]
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.OBJECT_ID=tbl.OBJECT_ID
INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.OBJECT_ID
AND p.minor_id=clmns.column_id
AND p.class=1
ORDER BY [Table_Schema] ASC,
[Table_Name] ASC,
[Column_ID] ASC,
[Name] ASC
[/font]
Best wishes,
Phil Factor
December 3, 2009 at 11:07 am
That was great helped me out so I can export properties and I added column definitions. Currently I am putting my descriptions in just saying MS_Description. Should I be using something more appropriate. Do you break apart your descriptions into more logical formats? If so can you give me an example so I do not stumble on this in a year?
December 3, 2009 at 11:38 am
[p]Yes. I put everything in MS_Description because this is the only thing that is accessible to SSMS. I then put a YAML structure into the string so I can do lists and sections.[/p]
[p]The section between the /** **/ is placed into, or updated to, the extended property by an automated process. [/p]
[font="Courier New"]
IF OBJECT_ID(N'IsSpace') IS NOT NULL
DROP FUNCTION IsSpace
GO
CREATE FUNCTION dbo.[IsSpace] (@string VARCHAR(MAX))
/**
summary: >
IsSpace string Function Returns Non-Zero if all characters
in s are whitespace characters, 0 otherwise.
example:
- code: Select dbo.IsSpace('how many times must i tell you')
- code: Select dbo.IsSpace(' <>[]{}"!@#$%9 )))))))')
- code: Select dbo.IsSpace(' ????/>.<,')*/
returns: integer: 1 IF whitespace, otherwise 0
**/
RETURNS INT
AS BEGIN
RETURN CASE WHEN PATINDEX(
'%[A-Za-z0-9-]%', @string COLLATE Latin1_General_CS_AI
) > 0 THEN 0
ELSE 1
END
END
GO
[/font]
Best wishes,
Phil Factor
December 3, 2009 at 12:35 pm
You lost me on that last post. I understand what yaml is and I can see if being beneficial. I am lost on how you are using it though. Is this for outputting the data into yaml? Currently I have two ways I output one is sharepoint compatible (excel) and the other I use codesmith to put it in html docs so i have define the whole DB if needed. Would Yaml some how replace those?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply