Developing Matt

My Technical Journal

Handy procs (9 of 25 from Don Kiely)

with one comment

What is the precision on varbinary datatype?  Heck, I don’t know.  I don’t even know how I would use varbinary.  But if I query sp_datatype_info I can tell you:  8000.

sp_datatype gives you a rundown of all the datatypes and some helpful facts about them. 

Last week we needed to update something across all tables.  To do the same in all databses use sp_MSforeachdb.  Use ‘?’ for the database name. (sp_msforeachdb “print ‘?'”)

For all the same tables in a database use sp_MSforeachtable “print ‘?'”  Inside the quotes add your command.  Careful!

Other interesting system stored procs that are just handy:

xp_dirtree ‘c:\program files’ (slow…but will give you the directory tree)
xp_fileexist ‘c:\temp\test.txt’    (checks to see if file exists obviously)
xp_getfiledetails ‘c:\temp\deleteme.txt’
xp_readerrorlog (reads error log)
xp_getnetname (returns machine name)

All of these and more I found from Don Kiely in an article titled Two dozen of my favorite stored procedures.  Very good article.  I always like his stuff.  He relates to the end developer the things that are most important handy tools that he uses.  I attended a performance tips and tricks (ado.net) class of his and enjoyed it muchly.

Advertisements

Written by matt

October 17, 2008 at 5:49 am

Posted in T-Sql

One Response

Subscribe to comments with RSS.

  1. Thanks for the good words, Matt! I’m glad you found the article and session useful.

    Just be careful of that undocumented stuff! :-)

    Don

    Don Kiely

    October 17, 2008 at 7:49 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: