SQL Soundings

Display All System-Named Constraints in All User Databases

2012-06-10T01:00:00.000-04:00

In my previous post, I mentioned that system-named objects get arbitrary names (like, "PK__Executio__05F5D74515DA3E5D"), and that this makes comparing database schemas difficult. To fix this problem, you have to rename the offending constraints, and to do that, you have to find them. So, this script displays all the system-named constraints in all the databases.-- Display all the system-named

Script to Drop and Re-Create Column Statistics

2012-06-09T14:02:00.000-04:00

If you're like me, you use a third-party tool to compare SQL Server database schemas. No matter how hard we try to keep schemas in synch, some little change always creeps in, and being able to confirm that two database schemas are exactly alike helps us sleep at night.Schemas can differ in important ways (who added that stored procedure?) or in seemingly trivial ways (okay, there's an extra space

Proc to Fix the Too Many Virtual Log Files Problem

2012-04-28T20:58:00.000-04:00

There could be a beast lurking in your log files, robbing you of performance, and you might not even know its name. But fear not - I'll give you a weapon to kill the beast.If you've never heard of "virtual log files" (VLFs), read these articles: they'll well-written, short, and extremely informative. You owe it to yourself to read the originals, but I'll sum them up briefly here. VLFs: A Quick

SSIS: "And If You've Made it Wrong..."

2011-11-05T21:52:00.000-04:00

When you start working with SSIS, there's one thing nobody mentions: you have to learn a new syntax for expressions. It's not T-SQL, it's not VB.NET, it's not JavaScript, it's not like anything you've made before. It's "SSIS Expression Syntax", and you can learn all about it at Integration Services Expression Reference. (And yes, I'm pretty sure "it won't keep you comin' back for more"!)I bring

How Much Data Is In That Column?

2011-11-04T21:35:00.001-04:00

This is a special-purpose script, but it still may be useful to someone, someday.Imagine you have many tables with similar filenames and the same column names. This might happen if the tables are created automatically by a Job on a daily basis, for example. For each column you specify, this script displays the declared width, the average number of characters stored, and the minimum and maximum

Adding Up the Logical Reads in the Output Window

2011-10-16T14:48:00.000-04:00

Probably the first thing I learned about query tuning is that "clock-on-the-wall" time is definitely not the thing you want to measure. The elapsed time a query takes can be influenced by countless factors (other processes running, disk speed, number of processors, amount of RAM, phase of the moon, a few hundred database settings) that it's too rough a metric for serious performance tuning.What

SSIS: Word-wrapping Annotations (Using Only Notepad)

2011-10-09T13:10:00.001-04:00

Remember that Twilight Zone episode, where a man sells his soul to the Devil for immortality, only to be sentenced to life in prison the next day?I'm learning SSIS (2005, for now), and have discovered, like all those before me, that Microsoft implemented Annotations, but... they don't word-wrap! Your Annotation must fit on one really long one line! Bwa-ha-ha-ha-ha-ha!Of course, I'm no longer

SSIS FTP Task: Directory is not specified in the file connection manager

2011-10-04T21:05:00.000-04:00

Are you getting this error message, been Googling for an hour, and nothing helps?Error: 0xC0029186 at {Your FTP Task}, {Your FTP Task Description}: Directory is not specified in the file connection manager {Your Variable Name}.If you entered the value for the source and/or target filename variable and enclosed it with double-quotes, try removing them. Worked for me.(Before coming over to the SQL

Cleaning User Stuff from the master Database

2011-09-29T16:43:00.000-04:00

This is an upgrade to a script I wrote a long time ago. It's handy when you find a system whose master database is somehow full of user objects. (I know that none of us ever make that mistake...)The biggest improvement is that this script now only generates DROP statement, it doesn't execute them. You have to do that yourself, and you should be sure to back up your master database (you do that

"Select Top 1000 Rows" Doesn't Show SPARSE Columns?

2011-08-04T22:48:00.049-04:00

A colleague pointed out something I'd never noticed about SQL Server Management Studio's (SSMS) "Select Top 1000 Rows" feature: it doesn't display SPARSE columns. This is not a bug, but rather by design: SQL Server tables can have up to 30,000 SPARSE columns: imagine the issues with viewing thousands of columns at a time! The annoying thing, however, is that no matter how few columns a table has

Instantly Find Size of Tables on Disk

2011-06-16T21:18:00.001-04:00

Here's a script to tell you how big your table(s) are, subtotaled by indexes, with a grand total. Includes LOBs, and excludes dropped pages. SELECT CASE WHEN GROUPING(t.name) = 0 THEN t.name ELSE 'GRAND TOTAL' END AS 'Table Name' , CASE WHEN GROUPING(i.name) = 0 THEN i.name ELSE 'TABLE TOTAL' END AS 'Index Name' , SUM(au.total_pages) * 8 / 1024

Easy Documentation: Does Anything Even Use That Index?

2011-06-11T22:08:00.002-04:00

I don't know if it's ever happened to you, but I always find myself wishing I had documented what the devil I was thinking when I added an index to a table. I mean, it's all well and good to create an index, but it's annoying when six months later you can't remember what it was for. Is anything actually using this index? Can I maybe drop it? Yes, yes, I suppose I could document my indexes,

Get Read-to-Write Ratio for All Indexes

2011-05-14T20:24:00.000-04:00

Here's a quick script to help you quantify how useful your indexes are, in the sense of how much they're used to speed up queries, compared to how much work it is for SQL Server to maintain them during inserts, updates, and deletes. This only reflects the usage since the last SQL Server boot.-- Displays the read-to-update ratio of all indexes. Good for finding ones that may not be needed.

Reclaim Space using DBCC CLEANTABLE

2011-05-08T19:05:00.000-04:00

Image by The Real Estreya via Flickr I just read Pradeep Adiga's excellent post on using DBCC CLEANTABLE to reclaim space left over after a column with a variable length type has been dropped from a table. According to Books OnLine, the variable length data types are VARCHAR, NVARCHAR, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY, VARBINARY(MAX), TEXT, NTEXT, IMAGE, SQL_VARIANT, and XML. If you

How to Hide Metadata Information Using Extended Properties

2011-04-25T00:37:00.000-04:00

Ok, a more honest title for this post might be, 'How to Clean Up the Mess After You've Hosed the Extended Properties on Primary Key Constraints', but that wouldn't fit. What I was trying to accomplish is a topic for another post. But while grumbling loudly while writing the code to fix what I had inadvertently done, I realized it could be used to "hide" information about tables. I say "hide", in

Find and Kill All System-Named Statistics

2011-04-07T21:21:00.000-04:00

I hate system-named objects. Some of this is to be expected from someone like me, who, depending on the decade, could be called "persnickety", "perfectionist", "OCD", or "The Anal Retentive Chef." But there's also a very practical reason to name all your database objects. If you're using a third-party tool to synch up database schemas, you may find that every time you copy a system-named

What About "Under-Used" Indexes?

2011-04-05T00:54:00.000-04:00

We've all spent time looking for unused indexes (right?), but today my boss asked about under-used indexes. Now, I wrote a script a while ago to find completely unused indexes, but finding indexes that were only being used a little was just different enough to require a new script. The script below displays all indexes, with the least-used (including unused) ones first, and totals for user and

ISNULL or COALESCE? Sometimes it Really Does Matter

2011-04-01T21:27:00.000-04:00

After seeing some poor performance in a query that I couldn't figure out, I ended up reading a lot of discussions about the "ISNULL / COALESCE" debate. Some people feel one is faster than the other, and some people like COALESCE because it's ANSI standard. (And they hold these opinions very strongly.) I always thought the two were identical (when just two values are involved, of course), but it

Monitor the Progress of Long-Running Events

2011-02-07T00:38:00.013-05:00

I never knew you could monitor the progress of long-running processes like DBCC CHECKDB, backups, shrinking files, rebuilding indexes, etc. until I read an offhand comment by Kalen Delaney (which I can't find now). Now I keep this script open in a SSMS window about 90% of my day! -- Displays the progress of several kinds of commands. See BOL. ; with cte1 as ( select command

Removing All Duplicate Tabs, Linefeeds, Returns, and Spaces

2011-01-31T13:58:00.002-05:00

The trace tables created by SQL Profiler are nice and all, but it's a pain to search through the TextData column. The code below uses a brute force method to replace all contiguous whitespace characters with a single space. This makes it possible to search the TextData column without differences in whitespace getting in the way. -- Removing All Duplicate Tabs, Linefeeds, Returns, and Spaces

Remove All Text Between Single Quotes

2010-08-24T19:53:00.000-04:00

There's a zillion posts and articles about how to escape single quotes in T-SQL, but what I wanted to do was strip out the characters that were inside the single quotes. After a ridiculous amount of Googling turned up nothing, I spent an even more ridiculous amount of time figuring it out. The code below is a complete, working example of removing the characters from between two single quotes

ASCII Bar Chart of Job History

2010-08-18T00:26:00.000-04:00

If you're like me, it's a lot easier to understand things with a picture. With that in mind, here's a script you can run as a Job which will print out a visual representation of the Jobs that ran in the last 24 hours. The results are emailed to whatever address you provide (near the end of the script). ------------------------------------------------------------------------------- -- Prints a

Scriplet: Display Connections Per User & Database

2010-08-07T23:14:00.000-04:00

A quick script to list out which users have how many connections with which databases. -- Display users' connections per database. SELECT DB_NAME(dbid) AS 'Database Name', COUNT(dbid) AS 'No Of Connections', loginame AS 'Login Name' FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame

Lost the "Database Tab" in Visio 2010?

2010-07-25T01:39:00.000-04:00

I've been using Visio since before Microsoft bought them, and I love Visio 2010, but the Microsoft Office help is worthless! Every dialog has a "context-sensitive" help button with a little question mark on it, but - psyche! - it always opens to the main Visio help page. Why not just search? Great idea! Try searching the Visio 2010 help for, oh, I don't know, something fundamental to Visio,

Determining if a Folder Exists Using xp_cmdshell

2010-06-24T23:49:00.001-04:00

The code below creates a stored proc that will report if a passed-in Windows folder exists or not. While it avoids using the undocumented xp_fileexist system proc, it uses xp_cmdshell, which in many places is the first thing that gets disabled because of the obvious security ramifications. Still, it's a nice hack if you're working in the file system a lot. IF OBJECT_ID('dbo.usp_FolderExist')