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 = CAST(D.Value AS nvarchar(128)) FROM ::fn_ListExtendedProperty('MS_Description', 'schema', 'dbo', 'table', N'column', 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: This handy tool is absolutely FREE, but any feedback would be appreciated. ![]() The tool, DBGizmo, also provides some of the features most commonly requested in SQL Server forums. There's a free tool available with a GUI editor for extended properties. I didn't know about the Extended Properties until I saw this article. I can assign the Extended Properties to the columns of a view, but the HTML seems to ignore them when generating the data dictionary. Tried this and it works great for the columns in my tables but not for views. How do i get the MS_Description extended propertie from a database ? How do we get the exteneded property for the tables only.īut how can I save it as a TEXT or non-HTML file ? Like first column is 1, second column is 2, third column is 3, etc. What is the "InPrimaryKey" column and why some of the tables return incremented value. You will be missing the descriptions, so take some time and start adding the ![]() The script will run without adding the extended properties, but obviously.It also gets backed up with the database. To worry about having multiple copies and trying to figure out which one isĬurrent. Since you are storing the documentation in the database, you never have.The script can be scheduled to run as a job, so you will never have to worryĪbout updating the documentation manually.WHERE (tbl.name = and exprop.class = 1) -I don't wand to include comments on indexes LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id AND exprop.minor_id = lumn_id LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id=fault_object_id LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id AND typ.user_type_id = typ.system_type_id LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = er_type_id LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id AND lumn_id = lumn_id AND idxcol.object_id = clmns.object_id AND 0 = idxcol.is_included_column LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id AND 1 =idx.is_primary_key INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id '' + CAST(clmns.is_identity AS VARCHAR(20)) + '' , '' + CAST(clmns.is_computed AS VARCHAR(20)) + '' , '' + CAST(clmns.is_nullable AS VARCHAR(20)) + '' , '' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '', '' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '', ![]() '' + CAST(CAST(CASE WHEN typ.name IN (N'nchar', N'nvarchar') AND clmns.max_length -1ĮLSE clmns.max_length END AS INT) AS VARCHAR(20)) + '', WHERE fkclmn.parent_column_id = lumn_idĪND fkclmn.parent_object_id = clmns.object_id '' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '', Select substring(cast(Value as varchar(1000)),251, 250) FROM WHERE A.major_id = name = 'MS_Description' and minor_id = 0 Select substring(cast(Value as varchar(1000)),1,250) FROM
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |