What's a more recent stored procedure, Development or Prod?

select

do.name, do.create_date, do.modify_date, po.create_date, po.modify_date


from Development.sys.objects do join Production.sys.objects po on do.name = po.name

where

do.type = 'P'

and

do.name not like 'asp%'

and

abs(datediff (hh, do.modify_date, po.modify_date)) > 2

and

do.modify_date > po.modify_date

order

by do.name

Find an index across databases

Some tools report back the name of an index without the name fo the database it's in... other folks may have an index in some of their SaaS databases, but not all. Here's one way to find them all.

 

 

declare dbs cursor for

select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb') order by name

go

open dbs

 

declare @name varchar(1000), @string varchar(2000)

 

fetch dbs into @name

 

while @@FETCH_STATUS = 0

begin

 

fetch dbs into @name

set @string = 'select * from [' + @name +'].sys.indexes where name like ''_dta%'''

set @string = 'if exists ( ' + @string + ') begin ' + 'select '''+ @name + '''' + @string + ' end'

 

print @string

 

end

 

close DBs

 

Deallocate dbs

Advice for newbies

Here's a list of things I talk to new IT folks about; they are things I didn't always get right over time, or things that new folks drove me nuts doing:

1)      Accurate is more important than fast

2)      Fast is good as long as it doesn’t violate rule #1

3)      Late is occasionally going to happen; late without warning management should not happen

4)      Try new things. Life is a learning experience.

5)      Ask for help if you’re not sure you’re going to get something right. See #1

6)      Show initiative. You are also being hired for your brain. Use it.

 

7)    You don’t have the whole picture.  If you need more of the picture in order to make smart decisions, ask for it.

New Speaking Event... Confio Webinar in May

Indexing for SQL Server Performance – Smart Practices for the DBA and Developer http://ow.ly/kj9Ug 

Date: Thursday, May 9, 2013
Time: 2:00pm ET/7:00pm GMT
Duration: 1 hour

I hope to see you there!

SQL Saturday, San Juan, 4/20/2013

Come say Hi!

How many rows are in your tables?

If you need counts on all of your tables (perhaps you are doing some data mining on a new server?), you probably don't want to run a "selcet count (*)" on all the tables -- it's time consuming and resoruce intensive.

Instead, try this:

SELECT sc.name +'.'+ ta.name TableName

 ,SUM(pa.rows) RowCnt

 FROM sys.tables ta

 INNER JOIN sys.partitions pa

 ON pa.OBJECT_ID = ta.OBJECT_ID

 INNER JOIN sys.schemas sc

 ON ta.schema_id = sc.schema_id

 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)

 GROUP BY sc.name,ta.name

 ORDER BY SUM(pa.rows) DESC

SQL Saturdays...

Looks like I'm going to be speaking at SQL Satuirday in Tampa in only a few weeks; then in Jacksonville a couple of months later; and possibly, in San Juan in between. (Also thinking about Manhattan in August... )

Be sure to come over & say "Hi!"

(Register here:  www.sqlsaturday.com )