Find Commmon SQL Objects (to drop)

We often inload a lot of data and use views to point to the most recent table. Periodically I've been deleting the old table with this script and thought I'd post it, hope it helps.

--// Find Commmon Objects

SELECT
DISTINCT
S.NAME
,'sp_helpText ''' + S.NAME +'''' [ExecToView]
,CASE
WHEN S.Type = 'P' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP PROC ' + S.NAME
WHEN S.Type = 'U' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP TABLE ' + S.NAME
WHEN S.Type = 'V' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP VIEW '
ELSE 'NOT IN NORM'
END [Action]
,CASE
WHEN S.Type = 'P' THEN 'Stored Procedure'
WHEN S.Type = 'U' THEN 'User Table'
WHEN S.Type = 'V' THEN 'View'
ELSE 'NOT IN NORM'
END [ObjectType]
FROM
SYSOBJECTS S (NOLOCK)
LEFT JOIN sys.sysdepends d ON d.depid = s.id
WHERE
--S.TYPE = 'P'
S.TYPE = 'U'
--S.TYPE = 'V'
AND S.NAME LIKE '%TableNamePrefix%'
ORDER BY
S.Name

Jury duty?

The man apparently wants to see me at 7:30 am. We'll see if I get lucky.

Ah-ha, I found the positioning issue

Apparently there isn't a tower talking within my area. I know they are there, but like this site say, not all towers
http://www.cellreception.com/towers/towers.php?city=irvine&state_abr=ca
"not every antenna is required to be registered with the FCC."

In other words it ain't going to give up any positioning info?

More on Cellphone positioning

Seem to be picking up steam...

http://electricpocket.com/findme/

http://fireeagle.yahoo.net/

http://www.navizon.com/navizon_v-gps.asp

http://www.google.com/mobile/gmm/mylocation/index.html

That what I got so far, read so much that I can't keep my eyes open any longer.

Feel so behind in the game, but this ain't my bag baby. Probably already in the works or still out on the web waiting for me to stumble across but...

Develop an app/dll that just spits out your approximate position based on cellphone triangulation, like google already does. Others can publish apps for install on your phone that takes advantage of this common dll. Tired, this may not be making a whole lot of sense, but the back of my head thinks its somewhat viable.

Think this would really level the playing field and allow for so many different people to throw out as many ideas as they can with this. Allowing the public drive what they ultimately like or would like to see more of.

Beuler? This can't possible make sense, gnite.

Send wife location of what bar I'm at, so she can pick me up.
Kids send their position to parents for pickup when at friends house or soccer game moved to different field.
I was here and here a pic i just took.
Post your position to a site that will plot your wandering.
Basically, tell, find or track.

Curious and intriguing... cellphone positioning

Not sure if I'll be able to actually do anything with this info, but you never know. I've been researching this for the past 3 hours now, because google gmm on my phone isn't showing "My Location" at home. Thought it may be a bug, and wanted to to know what info it used to determine position and how to get it.

Based on "myl:MCC:MNC:LAC:CID" my first 2 values are -1,-1, so I guess something broke or is not coughing up the info?

http://maps.alphadex.de/index.php?section=mylocation
>http://maps.alphadex.de/datafiles/fct0e1b117823ccc1a.txt
>http://maps.alphadex.de/datafiles/fct0e1b11782832f02.cs
"How to get CID/LAC and stuff on a Windows Mobile device?"
>http://maps.alphadex.de/datafiles/ril.cs

Man this guy is really into it. Kudos to you Neil.

SQL2005 Find Blocking process

Yesterday my office practically had a meltdown because our site was being unresponsive. Of course, this happened right when I left for the gym.

8 missed calls later, someone taps me on the shoulder, while I'm on the treadmill at 24hour, and informs me that the site is down. Are you kidding me?? It was a co-worker who was at lunch also, they all know I go to gym at lunch. Anyway....

Dripping with sweat, I tear through the streets praying the cops are on their doughnut break. It appeared that they were too.

RDP'd into server, notice cpu maxed, 300+ active connections, and at the same time 20+ IM's from people in the office telling me the site does not appear to be working. 2 minutes into it, I find a crap load of processes being blocked, thank you sp_who2. Found process hanging everything up. Killed it, waited 5 more minutes for process to roll back, then proceeded on to damage control.

Enlight of this I needed to get the blocks on the radar(visible) for others to look at in case I'm ever not around, like today at lunch.

On to google...searching..read..read... found.

http://www.zdnetasia.com/techguide/database-management/0,3800010795,62038701,00.htm
Find blocking processes using recursion in SQL Server 2005

Adapted code into my own flavor, below, but mad props to this guy for laying the ground work.

CREATE Procedure [dbo].[sp_FindBlocks]
AS
BEGIN
SET NOCOUNT ON;
--//Find Blocking process
DECLARE @Processes TABLE (
[spid] [smallint] NOT NULL,
[BlockingSPID] [smallint] NOT NULL,
[DatabaseName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[program_name] [nchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[loginame] [nchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Definition] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Host] varchar(255) null
)

INSERT INTO @Processes
SELECT
s.spid
,s.blocked
,DB_NAME(s.dbid)
,s.program_name
,s.loginame
,OBJECT_NAME(objectid)
,LEFT(CAST(text AS VARCHAR(MAX)),100)
,s.hostname
FROM
sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50;

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, Host, LoginName, ProgramName)
AS
(
SELECT
s.SPID
, s.BlockingSPID
, s.Definition
,ROW_NUMBER() OVER(ORDER BY s.SPID)
,0 AS LevelRow
, s.host
, s.loginame
, s.program_name
FROM
@Processes s
JOIN @Processes s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID
, r.BlockingSPID
, r.Definition
,d.RowNo
,d.LevelRow + 1
, d.host
, d.LoginName
, d.ProgramName
FROM
@Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)

SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
END

It's still a work in process, will repost when I get it nailed down to my liking.

TTFN

GOOGLE always amazing me

About a year ago I put google maps on my T-Mobile Dash, it was a cool novelty at first. Quickly became a pain when trying to find or do anything while in route or at a stop light. It soon lost its appeal and got booted from my phone.

Today, through a series of ...
Where canI find the xml schema for my phone's home screen XML?
Searching Google.
Many sites found, nothing with true definition though.
One site had banner for software (mobiumgps.com) that would display turn by turn info on phone with bluetooth gps module connected. Dim light bulb started flickering in head.

Wasn't google working on triangulating your position based on you relative position to cell towers your phone could talk to? hmm.....

YES!!! F'n SWEET. http://www.google.com/intl/en/press/annc/20071128_maps_mobile_my_location.html. I know, I know, where the heck have I been? Very very busy.

So now I got it on my phone, AGAIN and oh man this rocks. AND then i find out about the click-to-call feature, DAMN! http://googleblog.blogspot.com/2006/11/click-to-call-in-google-maps.html

Just keeps getting better.

Now I'm waiting for someone (google), to allow me to post/send/sms/whatever my current to others. Something in me thinks the guys at Brightkite.com should look into this.

Even though it's not as accurate as GPS, with the satellite view I can move the point to my exact location, and then if I could send, MAN that would be sweet.

Privacy, privacy, privacy... I know, but really, who do I need to hide from?

"Karma is like a boomerang, never sharpen it."

Poor man's SQL Profiling

While trying to nail down a perfomance issue, then execution plan was not really offering up any clues so I had to pull this out. Did what I needed, show the hog.

/* FOR DEBUG PURPOSES */
DECLARE @pStart DateTime, @pEnd DateTime, @pElasped INT, @pDebug bit
SELECT @pDebug = 1
/*
IF @pDebug = 1 SELECT @pStart = GetDate()

...Process to time....

IF @pDebug
BEGIN
SELECT @pEnd = GetDate(), @pElasped = DateDiff(ms,@pStart, @pEnd)
PRINT status + CONVERT(NVARCHAR,@pElasped)
END
*/

SQL for the Obsessive.

If you are obsessive compulsive, look into becoming a DBA.

SQL CASE WHEN

Hey kids, how many of you use 2 or more statements to update data on the same table? Where the critieria is based on the data contained in the same table which you are trying to update? "CASE WHEN" is a wonderful alternative.

I got your Partition right here >>>

I've concurred my SQL issues regarding performance and partitioning. I've got 2 years to implement the sliding window now. NOTE: When accessing data in the partitions, yes be sure to include the partitioned column, BUT, if your column is a date like most, use BETWEEN rather than DATEDIFF. I had a rough time trying to figure out why a query plan was not using my covering index.

TTFN

SQL CLUSTERED INDEX FYI

"if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also."
ref: http://technet.microsoft.com/en-us/library/ms190981.aspx

I missed that and am now paying for it with current wait time of 4 hours and 30 minutes and counting on a 112 million record partitioned table.

Watching the progress with:
SELECT
*
FROM
sys.partitions
WHERE
OBJECT_ID = OBJECT_ID('claimreport')
AND ROWS > 0
Order by
Partition_NUMBER,Rows desc, index_id

Be sureto monitor your log growth.

SQL SAFELY Shink all possible log files

--// SAFELY Shink all possible log files
--// RETURN RESULT TO TEXT. REVIEW AND EXECUTE
--// Adjust L.LogUsed AND L.LogSize as needed
SET NOCOUNT ON
If Object_Id('tempdb..#tmpLogInfo') is Not Null
Drop table #tmpLogInfo

CREATE TABLE #tmpLogInfo
(
databaseName sysname
, logSize decimal(18,5)
, logUsed decimal(18,5)
, status INT
)

INSERT INTO #tmpLogInfo
exec('dbcc sqlperf(logspace)')

SELECT
'--// ' + convert(nvarchar,L.databaseName)
+ ' LogSize: ' + convert(nvarchar,L.LogSize)
+ ' %LogUsed: ' + convert(nvarchar,L.LogUsed) + '
USE [' + convert(nvarchar,L.databaseName) + ']
' + (CASE WHEN recovery_model_desc <> 'SIMPLE' THEN '--// RECOVERY MODEL NOT SET TO SIMPLE: ' + L.databaseName + '
--// ALTER DATABASE ' + L.databaseName + ' SET RECOVERY SIMPLE

' ELSE 'DBCC SHRINKFILE (' + ms.name + ', 1)
GO' END)
FROM
#tmpLogInfo L
JOIN sys.databases db ON db.name = L.DatabaseName
JOIN master.sys.sysaltfiles ms ON ms.dbid = DB_ID(L.databaseName)
WHERE
L.databaseName not in ('master','tempdb','model','msdb')
AND ms.Status = 1048642
AND Right(ms.filename,4) = '.ldf'
AND L.LogUsed < 10

Drop table #tmpLogInfo