SQL Server Support Scripts

1. SQL Server Support Scripts How a little code can help with support.

2. Who am I and where can you find me • Chris Barba – Developer at Cimarex Energy • Blog:http://chrisbarba.wordpress.com • Twitter:@chrisbarba • LinkedIn:www.linkedin.com/in/chrisbarba/

3. Agenda • Provide some tools for your SQL toolbox. • Some scripts to help with development and some help with support (some help with both). • All of these scripts can be found on my blog. • It’s where I put stuff I don’t want to lose. • Items discussed today: • SQL Server 2008 Intellisense not correct • SQL Server 2008, can’t save changes to tables • How to tell which version of SQL you’re running • How to find out the last time your database was restored • How to tell what SQL objects have been added/updated recently • Record count of all tables in a database • Search for string across all fields in all tables in a database • SQL Server Keyword search across Stored Procedures, Functions, Triggers • Track Changes to objects in your database

4. SQL Server 2008 Intellisense not correct • Intellisense info gets populated and stored in a cache. • Sometimes it gets out of sync with reality • For ex, If the query window was opened before the table was created • How to fix • Ctrl+Shift+r • Warning: • Be sure SSMS is the active window, this keyboard shortcut is used in other applications. • Linkto blog

5. SSMS 2008 can’t save table changes • SQL 2008 Management Studio can’t save changes to tables • Microsoft recommends you script out all table changes • Here is what you can’t do • Change the Allow Nulls setting for a column • Reorder the columns • Change the column data type • Add a new column • How to fix • Tools -> Options -> Designers -> Tables and Designers • Uncheck the Prevent Saving Changes that require table re-creation • Warning: • This will conflict if you have Change Tracking turned off. • With this option off, if you change a table all tracking changes will also be delete. • Link to blog

6. How to tell which version of SQL you’re running • The features available to you depend on the version of SQL Server you are running. • Why does this matter: • Asked to develop functionality that might not be available on the DB version. • People might not actually know what version they have. • Demo Script • Super quick and easy method: • Select @@version • Link to blog

7. How to find out the last time your database was restored • When in an environment other than production you might not know how new the data is. • You can see the last time a database was restored. • Demo Script • Link to blog

8. How to tell what SQL objects have been added/updated recently • There are times when it helps to know what db objects have been added/updated recently. • Track changes in production • Know what changed during development • Demo Script • Link to blog

9. Record count of all tables in a database • This helps to get an idea about what’s normal in terms of the volume of data. • Sometimes you need a take a row count before and after doing something • Demo Script • Link to blog

10. Search for string across all fields in all tables in a database • There are times when you have data, but you don’t know where it is stored. • This script will search every field in every database for a value. • This helps when you have data and you don’t know where it’s stored. • Demo Script • Link to blog

11. SQL Server Keyword search across Stored Procedures, Functions, Triggers • There are times where you don’t know what proc, etc. is updating your table. • Also you may need to know if a string is hard coded. • Demo Script • Link to blog.

12. Track Changes to objects in your database • This script will allow you to track when/what/who makes changes to DB objects. • It creates an audit table and puts triggers on the database. • More • Warning:I don’t know where this triggers get created in the database. • You need permissions to create a table in a database. • Demo Script • Link to blog.