Browse by Tags
All Tags »
sql (RSS)
thanks to this article for the base script. i needed to get a list of all users and the roles they had in each db. this script is great in that you can use the raw base script (mine is slightly modified) and list things for every object instead of just...
it's depressing to me how many people that i run into today that have an excrutiatingly limited knowledge about the command line in windows, and yet they are considered experts on the platform. that aside, i figured i would share a couple of very...
select case when dbid = 32767 then 'resource' else db_name(dbid) end [db_name] , object_schema_name(objectid,dbid) [schema_name] , object_name(objectid,dbid) [object_name] , sum(usecounts) [executions] , max(max_execution_time) [last_execution_time...
Great useful base diag scripts! Snipped from http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry -- SQL Server 2005 Emergency Diagnostic and Performance Queries -- Glenn Berry 3-17-2008 -- Step 1 - Check Task Manager. Are...
select top 10 t.text , s.last_execution_time , * from sys.dm_exec_query_stats s cross apply sys.dm_exec_sql_text(s.sql_handle) t where t.objectid is not null and text like '%procname%' order by s.last_execution_time desc obviously replace procname...
EXEC sp_configure @configname = 'Show Advanced Options', @configvalue = 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure @configname = 'clr enabled', @configvalue = 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure go
A colleague of mine that is more familiar with Oracle than MSSql asked me how to create a CONNECT BY query in TSQL. CONNECT BY queries provide for recursive results or views on hierarchical data . The answer is to use a Common Table Expression or CTE...
DBCC FREESESSIONCACHE DBCC FREEPROCCACHE DBCC FREESYSTEMCACHE('ALL') CHECKPOINT DBCC DROPCLEANBUFFERS
sp_depends will show you the dependencies for an object in sql, but here's a really cheap way to execute that for everything. i had someone ask me how to do this in sql the other day so here it is. you can also just tear into sp_depends and use that...
this is a little unique of a setup. i'm having to update a varbin field, but the data that is coming in from the other sources is being cast from xml. so i'm sort of replicating the stupid thing we are doing on the other side, but it seems like...
//reference this metholodogy justification at // http://www.lacoude.com/Docs/public/public.aspx?doc=SQL90XML.PDF int bufferSize = 0; byte [] buffer = new byte [ 8040 ]; string update_sql = @"update tablewithblobs set blobcolumn .write(@a,null,0)...
i meant to write a long post about this but i never did. if you have to perform an alter column on a large blob column, sometimes all your space doesn't come back, you may want to issue a dbcc cleantable command on the table.
As I mentioned in a previous post , David Hayden sat in the front row and gave me an especially hard time about ‘set nocount on’ as a tip. I don’t remember the exact words, so I’m going to have to roughly paraphrase. His complaint was that some data access...
You can download the slides I used for my "SQL Tips n' Tricks for Developers" talk at the Tampa Code Camp (7/14/2007) from the following location: http://drowningintechnicaldebt.com/files/folders/royashbrook/entry403.aspx Here's the...
Recently we had an issue at work where nightly MS SQL Server DB index defrags were taking a long time. Upon looking at the logs it appeared that almost all of the tables were fragmented quite often. It didn't take long to ask if anyone ever modified...
More Posts
Next page »