MSSQLTips

This post is related to this tip: Create a SQL Server Data Dictionary in Seconds using Extended Properties

http://www.mssqltips.com/tip.asp?tip=1499

It gave me the error when I tested it on AdventureWorks

"The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid."

 

Anyway, I use RedGate's SQL Doc to generate DB docs

http://www.red-gate.com/products/SQL_Doc/index.htm 

 Jerry

 Write your results to text instead of grid

How do you specify writing results to text instead of grid?

You can go to Tools -> Options -> Query Results -> SQL Server -> General, and change the default destination to "Results to test".

I'm not crazy about changing my results default, but I could not find a command that I could execute as part of the script.

On top of the SSMS Toolbar (the same one that has the red Execute button), there are 3 icons near the right

Results to Text

Results to Grid

Results to File

 

just click on the one you need

hi i have use this script of link http://www.mssqltips.com/tip.asp?tip=1499

but i have not dispaly row and colomn of table only dispaly table name

That is becuase you do not have any extended properties set on the tables.

thanks for repaly

but i set  the extended property  of some tables in databse  .

Nice!  Elegant, and the price is right ;) Two tweaks may be useful to others:  I’m way behind on column descriptions so moved the exprop.class = 1 from the WHERE to the ON to show columns that lack descriptions. Changed @TableName from v(50) to sysname – I got long table names. I’m off to create http endpoints to let my servers publish their own DD – on demand!

 

do you have any suggestions for tools that can be used by end users to maintain the extended properties? I'd like to add the extended properties to the db objects in ssms and then have some type of form that people with domain specific knowledge could use to populate the properties. This is an existing db that I am trying to get documented.

TIA
Dean

Well, you'd have to create the GUI front end yourself, but to extract the current description from a field call the following (let the column name be NULL to get the table description):

        SELECT @OldDesc = CAST(D.Value AS nvarchar(128))
            FROM ::fn_ListExtendedProperty('MS_Description', 'schema', 'dbo', 'table', @TableName, N'column', @ColName) D;

In order to update a description, you first have to remove the original one (if it exists), then replace it with a new one:

         IF NOT (@OldDesc IS NULL OR @@RowCount = 0)
            EXEC sp_dropextendedproperty N'MS_Description', N'schema', N'dbo', N'table', @TableName, N'column', @ColName;
        EXEC sp_addextendedproperty N'MS_Description', @NewDesc, N'schema', N'dbo', N'table', @TableName, N'column', @ColName;
 

I hope this points you in the right direction.

--Aaron