THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Don’t learn SSDT, learn about your databases instead

Last Thursday I presented my session “Introduction to SSDT” at the SQL Supper event held at the offices of 7 Digital (loved the samosas, guys). I did my usual spiel, tour of the IDE, connected development, declarative database development yadda yadda yadda… and at the end asked if there were any questions. One gentleman in attendance (sorry, can’t remember your name) raised his hand and stated that by attempting to evangelise all of the features I’d missed the single biggest benefit of SSDT, that it can tell you stuff about database that you didn’t already know.

I realised that he was dead right. SSDT allows you to import your whole database schema into a new project and it will instantly give you a list of errors and/or warnings pertaining to the objects in your database. Invalid references (e.g a long-forgotten stored procedure that refers to a non-existent column), unnecessary 3-part naming, incorrect case usage, syntax errors…it’ll tell you about all of ‘em! Turn on static code analysis (this article shows you how) and you’ll learn even more such as any stored procedures that begin with “sp_”, WHERE clauses that will kill performance, use of @@IDENTITY instead of SCOPE_IDENTITY(), use of deprecated syntax, implicit casts etc…. the list goes on and on.

I urge you to download and install SSDT (takes a few minutes, its free and you don’t need SQL Server or Visual Studio pre-installed), start a new project:

image

right-click on your new project and import from your database:

image

and see what happens:

image

You may be surprised what you discover. Let me know in the comments below what results you get, total number of objects, number of errors/warnings, I’d be interested to know!

@Jamiet

Published Monday, November 11, 2013 6:45 PM by jamiet
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Lee Everest said:

Thanks did not know. U rule dude!

November 14, 2013 11:47 AM
 

Stephen B said:

Cant remember what you guys said was your records. but first one ive tried.

Error 201 The maximum number of 200 errors has been reached.

684 total errors,

119 total warnings,

and

0 total messages were encountered

I guess the next question is how do i get access to the other 400+ errors :/

November 20, 2013 9:55 AM
 

Stephen B said:

To view more Errors:

Tools -> Options 0> SQL Server Tools -> general :

Maximum number of erors and warnings to display per project.

Upped it to 10,000 and fresh new wall of red and yellow has filled my screen.

November 20, 2013 10:01 AM
 

Peter Schott said:

Stephen,

What I'd likely start doing is clearing up the errors and warnings. Some warnings can be suppressed (perhaps an object in another database that you know exists or similar). The errors need to be addressed before you can build successfully. Double-clicking the error row should take you to the problematic file/line to address.

 There may be an option for the IDE that limits the number displayed, but I think you'd find that addressing those errors will make a big difference. In my experience, clearing up one error often results in several others resolving because they're very similar.

November 20, 2013 10:02 AM
 

jamiet said:

Stephen,

Awesome, sounds like you have some work to do :)

I'll echo what Peter said, many errors/warnings are symptoms of the same thing so eliminating one will nearly always eliminate a bunch of others.

JT

November 20, 2013 10:37 AM
 

Miaroz said:

If you are working from a disconnected database you may get a bunch of error messages and warnings for perfectly legitimate references. For instance all references to sys objects or linked server objects generate errors because they are not in the disconnected database files.

March 17, 2014 10:09 AM
 

Miaroz said:

If you have it script Security objects, like CREATE statements for Sql Logins it will generate errors for any references to Windows logins. If you are satisfied that these security objects are correct you can get rid of the error messages by commenting out the create statwment.

March 17, 2014 10:22 AM
 

Miaroz said:

Also, if you are calling a stored procedure from another parent procedure, and the parent creates a temporary table which is references in the child procedure these references will generate error messages. See Erland Sommarskog's How to Share Data Between Stored Procedures blog on http://www.sommarskog.se/share_data.html for more information about this technique.

March 17, 2014 10:36 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement