March 17, 2011 at 10:02 am
I have a presentation I've given a couple times related to metadata and extended properties:
http://code.google.com/p/caderoux/wiki/LeversAndTurtles
Using it to fine tune code generation so that code can be re-generated without re-applying manual tweaks is very powerful. You can also use it to manage system self-tests.
I have applied to give this talk again at SQL Saturday in Pensacola and Baton Rouge.
Adding some layers of utility functions and views can help to simplify the awkward "API" which SQL Server provides. I liked this article, because it gave a good indication of the problems with "levels". In my shell API, I wrapped the levels with dotted identifiers (schema.table.column) to make things more uniform.
March 17, 2011 at 10:42 am
Great piece!
Meta Data / Extended Property question:
I have a PDF file (Man I hate PDF) that has thousands of details on Tables and columns for a very large DB (schema wise, there are over 2,000 tables) and I woudl desperately like to find a way to get that info form the PDF into SQL Server Extended properties.
ANyone know of a good way to get Meta data (sometimes called a data dictionary) out of a PDF and into something more easy to insert into t-SQL code? The PDF currently uses an Excel/Spreadhseet/table like structre; at least thats they way its presented. I have no diea how PDF works internally and so its looking like its in a table may not mean it any easier to export then if it were presented in free form style.
Kindest Regards,
Just say No to Facebook!March 17, 2011 at 10:54 am
Hi Adam,
The following sounds like a good reason: "tracks object evolution automatically".
Thanks for the answer.
AK
March 17, 2011 at 11:09 am
[font="Verdana"]Adam,
I read your article and enjoyed every bit of it. Your writing style is superb and is consistent with the word “Introduction” from the title. I have read many articles about extended properties to end up with more questions after reading them, but your article is the best I have read at this level. You clearly described the basics of MS SQL Server extended properties and their possible use.
Now I have to read all your other articles and will be waiting for the next two on the subject of Extended Properties.
[/font]
[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]
March 17, 2011 at 11:26 am
Enjoyed the article right up to the point where it linked to the SQL Server documentation on the feature, and felt compelled to look. Don't bother. Looks like documentation I would have written.
March 17, 2011 at 12:22 pm
Hi YSLGuru,
No idea on this one, I am afraid - sorry!
Adam
March 17, 2011 at 12:24 pm
Thanks for the encouragement, Sal!
March 17, 2011 at 12:34 pm
Great article Adam.
ATBCharles Kincaid
March 17, 2011 at 12:40 pm
So here is a couple of questions.
Let's say that there is a tool that lets you:
Log into a SQL server.
Update the description extended properties on your tables and columns.
Lets you export the same to an XML file.
Lets you import the same from an XML file.
Would such a tool be worth say $5 US? What else must it do to be worth that?
ATBCharles Kincaid
March 17, 2011 at 12:53 pm
Nice article. Thanks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2011 at 1:25 pm
Hi Charles,
Thanks for the feedback. As to a tool to manage documentation better - I would prefer to see Microsoft extend the product to include this.
Regards,
Adam
March 17, 2011 at 1:27 pm
Alexander Kuznetsov (3/17/2011)
Hi Adam,The following sounds like a good reason: "tracks object evolution automatically".
Thanks for the answer.
AK
The only problem (re: object evolution) I've found is in SQL Server 2005 on user-defined functions, the ALTER FUNCTION statement would cause extended properties to be lost. I think this was fixed in 2008. This is not a problem with ALTER PROCEDURE.
Renaming objects or changing schemas seemed to work fine in SQL Server 2005.
March 17, 2011 at 1:31 pm
Charles - have you looked at http://datadictionary.codeplex.com/ - free, and very good!
March 17, 2011 at 2:47 pm
YSLGuru,
ANyone know of a good way to get Meta data (sometimes called a data dictionary) out of a PDF and into something more easy to insert into t-SQL code? The PDF currently uses an Excel/Spreadhseet/table like structre; at least thats they way its presented. I have no diea how PDF works internally and so its looking like its in a table may not mean it any easier to export then if it were presented in free form style.
Internally, PDF is a compressed PostScript file (with proprietary add ons), and as far as I can tell there isn't any good way to extract the data out.
However, In theory, one could print the pdf to a PostScript file (e.g. set up a postscript printer and change the destination to be a file) and then use the postscript language to extract the data out. There may be converters to turn the postscript into something more friendly but I've never had occasion to look for any. It might even be possible to skip the postscript and set up some sort of line printer to create the file and parse that out after stripping out the control characters. If you decide to try this use the oldest printer driver you can make work (The Apple Laserwriter has historically been a good choice for a postscript printer driver). Tables might not be too bad but the more formatting and objects that exist in the pdf the harder it will be to parse out the file.
After writing all of that, I thought of a potentially much easier way:
OCR.
Good Luck,
March 17, 2011 at 4:18 pm
Another trick is to set up a Generic/Text Only printer on your PC and redirect that to a file.
ATBCharles Kincaid
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply