For those of you who may not have known, there’s a cool trick you can do in Visual Studio or SSMS; the alt-drag marquee. Many of these functions can be handled with fine and replace with or without regular expressions, but sometimes those are more trouble than it’s worth.

The basics of alt-drag are this. Say I have a block of text, such as a comment block of a procedure which I want to replace all at once. Hold alt and drag to the bottom right, and you get a marquee area which respects x/y coordinates rather than just lines and indentation.

Now the cool part. If you’ve ever used copy-paste with these marquees, you’ll find they work… oddly. However like any good Jedi, you can turn your enemies weakness into your strength.

Say for example, I forgot to type select statements for a derived table like this:

You could use find replace to replace “all” with “all select’, you could use a series of copy/paste commands and down arrow keys, or this:

Create a zero-width vertical marquee and start typing. Every text line which is crossed by the marquee will begin typing there (it works with ctrl-v_ as well.

Find What Filegroup a Given Index is on.

If you use the SQL Server system function sp_helpindex, you'll get back table information as well as index information, which includes a concatenated string which tells you what filegroup the index is on. I have an issue right now where we're doing cleanup of tables which had indexes created on the wrong filegroups, so I wanted a way to programmatically determine this information without having to run sp_helpindex on each table. To my surprise, I wasn't really able to find an article online for how to do this. Plenty of articles on moving the indexes to a new filegroup, which were helpful since I have to do that, but not for this initial research stage. With that in mind, I'd like to show you what I came up with.

    select 
        FilegroupName = ds.name, 
        IndexName = i.Name,
        IndexType = i.type_desc,
        TableName = t.Name
    from sys.indexes i
    inner join sys.data_spaces ds
        on i.data_space_id = ds.data_space_id
    inner join sys.tables t
        on i.object_id = t.object_id
    where i.type in (1, 2, 5, 6)

I got this basically by using sp_helptext on sp_helpindex.