Some usefull (internal) SQL scripts

This is a small collection of usefull (internal) SQL scripts.

Search all stored procedures

If you need to search all stored procedures to find the procedures that contain a particular table or column.

DECLARE @searchText VARCHAR(200) = '<table or column name>'

SELECT [name], [create_date], [modify_date]

FROM SYS.PROCEDURES

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+ @searchText +'%'

AND [type] = 'P'

ORDER BY [name]

Search all tables

If you need to search all tables to find the tables that contains a particular column name.

DECLARE @searchText VARCHAR(200) = '<column name>'

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name

FROM SYS.TABLES AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%' + @searchText + '%'

ORDER BY schema_name, table_name

Find tables with name

If you need to find all the tables which contains a specific text in the name

DECLARE @searchText VARCHAR(200) = '<some text>'

SELECT Name, create_date, modify_date

FROM SYS.TABLES

WHERE name LIKE '%'+ @searchText +'%'

Indexes

When examining the execution time for complex queries, one can get information about which indexes are defined for tables in a given database.

SELECT

schema_name(schema_id) AS SchemaName, 

OBJECT_NAME(si.object_id) AS TableName, 

si.name AS IndexName,

(CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) AS PK,

(CASE is_unique WHEN 1 THEN '1' ELSE '0' END) + ' ' +

(CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

'' AS 'Type',

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) AS Key1,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) AS Key2,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) AS Key3,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) AS Key4,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) AS Key5,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) AS Key6

FROM SYS.INDEXES AS si

LEFT JOIN SYS.OBJECTS AS so ON so.object_id=si.object_id

WHERE index_id > 0 -- omit the default heap

AND OBJECTPROPERTY(si.object_id, 'IsMsShipped') = 0 -- omit system tables

AND NOT (schema_name(schema_id) = 'dbo' 

AND OBJECT_NAME(si.object_id) = 'sysdiagrams') -- omit sysdiagrams

ORDER BY SchemaName, TableName, IndexName

Get specific index usage

It is possible to find out which of the defined indexes are being used. The following script retrieves statistics for the indexes, where you can see how many times they have been called.

SELECT

OBJECT_NAME(S.OBJECT_ID) AS [OBJECT NAME],

I.[NAME] AS [INDEX NAME],

I.Type_Desc AS [Index Type],

USER_SEEKS,

USER_SCANS,

USER_LOOKUPS,

USER_UPDATES

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = S.OBJECT_ID 

AND I.INDEX_ID = S.INDEX_ID

WHERE I.Name IS NOT NULL

AND OBJECT_NAME(S.OBJECT_ID) = '<table name>'

ORDER BY S.OBJECT_ID, USER_SEEKS DESC, USER_SCANS DESC

How to: Move a TFS folder or branch

It is possible to move a branch or folder in TFS - instead of copying the folder manually in the filesystem. The benefit of using TFS to move a folder is that it preserves the full history (and the parent/child relationship if its a branch) when merging.

Right-click on the folder to be moved and select 'Move...'

A dialog box appears letting you enter (or select) the new path and foldername.

Done. It's that easy - and the history is preserved.

Exception handling: The difference between 'throw' and 'throw exception'

There is a huge difference in how you handle exceptions and perhaps you do not even notice it.

The following two blocks of code looks almost the same - but they are quite different.

catch (Exception ex)

{

     throw ex;

}

catch (Exception)

{

     throw;

}

Try it out

Create a console application and use the following code.

using System;
 
class Program
{
    static void Main(string[] args)
    {
        try
        {
            CallFailingMethodAndPassException();
        }
        catch (Exception ex)
        {
            Console.WriteLine("\nStackTrace for method with 'Throw':");
            Console.WriteLine(ex.StackTrace);
        }
 
        try
        {
            CallFailingMethodAndThrowException();
        }
        catch (Exception ex)
        {
            Console.WriteLine("\nStackTrace for method with 'Throw ex':");
            Console.WriteLine(ex.StackTrace);
        }
 
        Console.ReadKey();
    }
 
    static void CallFailingMethodAndPassException()
    {
        try
        {
            CallFailingMethod();
        }
        catch (Exception)
        {
            throw;
        }
    }
 
    static void CallFailingMethodAndThrowException()
    {
        try
        {
            CallFailingMethod();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
 
    static void CallFailingMethod()
    {
        FailingMethod();
    }
 
    static void FailingMethod()
    {
        throw new Exception("Throw an Exception");
    }
}

The result is as follows:

StackTrace for method with 'Throw':
at Program.FailingMethod() in c:\Code\POC\Exceptions\ConsoleApp\ConsoleApp\Program.cs:line 61
at Program.CallFailingMethod() in c:\Code\POC\Exceptions\ConsoleApp\ConsoleApp\Program.cs:line 56
at Program.CallFailingMethodAndPassException() in c:\Code\POC\Exceptions\ConsoleApp\ConsoleApp\Program.cs:line 38
at Program.Main(String[] args) in c:\Code\POC\Exceptions\ConsoleApp\ConsoleApp\Program.cs:line 9

StackTrace for method with 'Throw ex':
at Program.CallFailingMethodAndThrowException() in c:\Code\POC\Exceptions\ConsoleApp\ConsoleApp\Program.cs:line 50
at Program.Main(String[] args) in c:\Code\POC\Exceptions\ConsoleApp\ConsoleApp\Program.cs:line 19

As you can see the first method call returns the full stacktrace and the second method call only returns a partial stacktrace. So consider carefully how you handle exceptions in your code.

How to: Move (migrate) a shelveset from one branch to another branch

If you want to move changes from one branch to another - when a merge is not possible - you can make a migration. 

The migration is made using the following TFS Power Tools command - executed via a Visual Studio command prompt from the target branch:

TFPT unshelve "<shelveset name>[; <shelveset creator name>]" /migrate /source:<source branch> /target:<target branch>

If you are migrating your own shelveset you don't have to enter the name of the selveset creator. To migrate the shelveset of another user you have to specify the displayname of the shelveset creator.

During the migration process a windows will appear showing the state of each file in the shelveset. You will then be able to select what to do with each file; ignore any changes, use the shelveset file or merge the files.

The changes are not automatically checked in - you have do a manually checkin afterwards. The files can be found as pending changes.

Example

TFPT unshelve "My Shelveset" /migrate /source:$/MyProject/Hotfix /target:$/MyProject/Development

The result of this command is that the files in "My Shelveset" (creted by myself) are moved from the "$/MyProject/Hotfix" branch to the "$/MyProject/Development" branch. 

Only shelvesets

You cannot move one (or more) changeset to another branch - only shelvesets can be moved. To move a changeset, you have to make it a shelveset - see: How to: Create a shelveset from a changeset.

How to: Create a shelveset from a changeset

It is not possible directly, to make a shelveset from a changeset! To achieve this you can follow these steps:

1. Find the changeset and make a rollback on it
2. Check in pending changes (the files that have been rolled back)
3. Take a note of the new changeset number
4. Create a rollback of the new changeset
5. Create a shelveset of the pending changes (Preserve pending changes locally)
6. Do a check in of the pending changes