Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Useful SQL templates for SQL SERVER 2008 R2

Posted on 2013-01-21
11
Medium Priority
?
185 Views
Last Modified: 2013-01-24
Hi Experts,

 I come across a template for Transaction in below path. I would like to have such useful templates more. Could you please share with me if you have?

http://www.codeproject.com/Tips/283065/SQL-Server-2008-Transactions-Usage-Template.aspx

  Some of the mostly useful scripts such as given below are added as template in my SSMS. It is useful. I would like to get more such kind of scripts. If you have please do share with me.

To identify text in SPs:


select dISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%update%mytable%' 

Open in new window

0
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 38800999
This is a pretty big open ended question. Most DBAs are likely to have a large collection of scripts in their toolkits.

SSMS has it's own set of templates which can be useful - go to view>Template Explorer or Ctrl+Alt+T

http://msdn.microsoft.com/en-us/library/ms179334(v=sql.105).aspx

Alternatively you could ask for more specific scripts or templates and I'm sure you'll find what you need.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 1000 total points
ID: 38802586
Agreed - there are more out there than I can include, but here are some links to get you started on a solid collection of management scripts:

http://dave.dk/sql-server-tools-and-scripts/
http://wiki.lessthandot.com/index.php/Category:Microsoft_SQL_Server
http://lukencode.com/2010/03/26/6-useful-sql-sever-scripts/

Also, if you want to know everything there is to know about a SQL Server, it's configuration state, and potential warning signs, all in one place, check out Brent Ozar's sp_BLITZ:

http://www.brentozar.com/blitz/

Especially if you're taking over a server you've not managed before, it's critical in getting you a  snapshot of the state of things as quickly as possible.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38802761
Hi,

Something I've done is to personalise those templates - add fields, default others, and add some boilerplate. That collection I put in a folder with my name.

While its great to have a big collection, there isn't anything useful about a template you don't use imho or don't understand.

Regards
  David
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38802788
Agreed - those templates are great and save time, but you have to make sure you understand what they're doing (or, at minimum, understand what the results actually mean). I've got them in a folder on my workstation that watched with Dropbox, so the most up to date copies I work with are always available whenever I need them. It took me a while as an early DBA to realize that typing the same code over and over wasn't a time-saver - it's not too long because a 15 minute task you repeat constantly is worth the 2 hours it takes to create a solid script and stow it away.

Even better when people smarter than me put the scripts together and publish them for general consumption. Soak up what you can!
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38804263
Thanks for your valuable information. What I'm looking for as developer level. Not at DBA level. Yes, I do know microsoft already provided some templates for basic operations.
Except that as a developer we do search some table used in SP instead of using view dependency of the table (I mentioned in question itself). Like this if you perform any other kind of operation as a developer using your own templates(or refer some resources from where I could get them) please do share with me.  I know my question is not specific - means I believe that from experts I could get more useful templates which would save my time.

If you have please do share with me. Otherwise I'm happy with the information given here. I could give points!!
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38804291
I could say one example. The script which clear cache.

I tested a SP with some parameters. I calculate time to execute. I run with other parameters, it would be fast as because of the cache. I would like to clear the cache at particular time. I look for script for that.
0
 
LVL 10

Assisted Solution

by:Matt Bowler
Matt Bowler earned 600 total points
ID: 38804296
DBCC DROPCLEANBUFFERS will clear the buffer pool
DBCC FREEPROCCACHE clears the plan cache
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38804343
Interesting.  @MattSQL - Could you please explain what is buffer pool and what is plan cache. Difference b/w them?
0
 
LVL 10

Assisted Solution

by:Matt Bowler
Matt Bowler earned 600 total points
ID: 38804350
The buffer pool contains data pages that are read into memory, the plan cache contains prepared query plans that could be reused.

Clearing the buffer pool is useful when testing. For example if your query retrieves a bunch of data, after it is run the first time the data will reside in memory (in the buffer pool) and subsequent runs will be much faster.

The plan cache stores prepared query plans. Again, on the first run of a query SQL Server will pass the query to the optimiser to prepare a query plan. Subsequent runs can use the plan from the cache and hence be faster.

CAVEAT: I have simplified things a bit here - both the plan cache and buffer pool are huge topics in SQL Server....
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38804369
Thanks. Got idea. You have any other useful snippets that could be used as templates? If so, please do share.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38814085
I've requested that this question be closed as follows:

Accepted answer: 0 points for EaswaranP's comment #a38804369
Assisted answer: 134 points for ryanmccauley's comment #a38802586
Assisted answer: 133 points for MattSQL's comment #a38804296
Assisted answer: 133 points for MattSQL's comment #a38804350

for the following reason:

Even I did not get more developer handy templates the given answers are informative.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question