Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

extended property for all columns of all tables Expand / Collapse
Author
Message
Posted Friday, May 9, 2008 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 12:22 AM
Points: 4, Visits: 12
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
Post #497713
Posted Tuesday, November 24, 2009 6:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 577, Visits: 2,503
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





Best wishes,

Phil Factor
Simple Talk
Post #823836
Posted Thursday, December 3, 2009 11:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 4:24 PM
Points: 418, Visits: 603
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?
Post #828376
Posted Thursday, December 3, 2009 11:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 577, Visits: 2,503

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.


The section between the /** **/ is placed into, or updated to, the extended property by an automated process.



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



Best wishes,

Phil Factor
Simple Talk
Post #828389
Posted Thursday, December 3, 2009 12:35 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 4:24 PM
Points: 418, Visits: 603
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?
Post #828436
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse