extended property for all columns of all tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » extended property for all columns of all...

extended property for all columns of all tables

Rate Topic

Display Mode

Topic Options

Author

Message

enrique.prados

Posted Friday, May 09, 2008 4:21 AM

Forum 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

Phil Factor

Posted Tuesday, November 24, 2009 6:40 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 533, Visits: 2,285

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

JKSQL

Posted Thursday, December 03, 2009 11:07 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:20 PM
Points: 392, Visits: 548

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

Phil Factor

Posted Thursday, December 03, 2009 11:38 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 533, Visits: 2,285

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

JKSQL

Posted Thursday, December 03, 2009 12:35 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:20 PM
Points: 392, Visits: 548

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 »

Permissions