1 / 25

Please help us! Thank our Sponsors:

Please help us! Thank our Sponsors:. SQL Server Scripts for your Toolbox. Chris Barba Senior Developer Analyst Cimarex. Please Be Courteous!. Session Objectives and Takeaways. Session Objective(s): Provide some scripts for your SQL Toolbox Not going to review each script line by line

rico
Download Presentation

Please help us! Thank our Sponsors:

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Please help us! Thank our Sponsors:

  2. SQL Server Scripts for your Toolbox Chris Barba Senior Developer Analyst Cimarex

  3. Please Be Courteous!

  4. Session Objectives and Takeaways • Session Objective(s): • Provide some scripts for your SQL Toolbox • Not going to review each script line by line • Could be better ways of doing some of this • Some scripts help with new development, some help with support (some help with both) • You can use this scripts immediately • Unless noted you don’t need elevated permissions to run

  5. SSMS 2008 Can’t Save Table Changes • Any change that causes a table to be drop and recreated can not be made. • Microsoft recommends scripting all table changes • Here’s how to fix • Tools -> Options -> Designers -> Tables and Designers • Uncheck “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 deleted.

  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. • Super quick and easy method: • Select @@version

  7. When was 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.

  8. 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

  9. Row 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

  10. Search for column names across all tables • There are times when you can’t remember what table holds a specific field • This script will return any tables containing the specified column name

  11. 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.

  12. 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. • This script will search across all your tsql code for a specific string.

  13. Get Database information • Returns information about each database on the server. • Returns information like file sizes, recovery model, etc.

  14. Loop through all tables • This is an undocumented stored procedure. • You won’t see this sp in SSMS. • Makes looping through all tables simpler • This is way easier than how I used to loop though all tables • sp_msForEachTable

  15. Loop through all databases • This is an undocumented stored procedure. • Allows run statements/commands against all databases on a server • sp_msForEachDB

  16. List all triggers • I avoid triggers because I always forget they are there. • This script will return all triggers on a database

  17. Get list of DB objects • List all tables • List all columns • List all default values • List all foreign keys • List all Primary keys • List all stored procs (with create scripts) • List all views (with create scripts) • List all synonyms

  18. List all tables views procs • Just need a quick list of all tables and views in a database • EXEC sp_tables

  19. Grant exec to all stored procedures • Easy way to grant execute permissions to all stored procedures. • Instead of granting per user, just add users to role. • You won’t have to remember to add permissions to new stored procedures

  20. Last Nights Job Run Status • This summary script will return all the jobs that ran last night and their status (success or failure). • I’ve used this script to email in the morning.

  21. 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. • Warning:You need permissions to create a table in a database.

  22. Instead of Triggers • Override the standard actions of triggering statements • Insert, Update, Delete • Performs additional actions before the events. • Ignore parts of a batch • Take alternate action if an error occurs

  23. Additional Resources • You can find all these scripts on my blog along with this presentation • http://chrisbarba.com • Email me if you have any trouble or questions • chrisbarba@gmail.com

  24. Please Complete An Evaluation FormYour input is important! You can access Evaluation Forms at: http://TulsaTechFest.com Fill them out! You can win additional prizes! Like a $50 Best Buy Gift Card!! Winner drawn – Midnight, Sun Oct 13th!

  25. Please help us! Thank our Sponsors:

More Related