SQL Query to Find Column From All Tables of Database

USE AdventureWorks
GOSELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_nameFROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_IDWHERE c.name LIKE '%EmployeeID%'ORDER BY schema_name, table_name;

Adding Set of Columns by Default on SQL Table Creation

Here is the easy to way to add a set of default columns such as Created User, Modified Date etc. to be added on table creation. Just run the following query.

ALTER TRIGGER TRG_TABLES
    ON DATABASE
    AFTER CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TABLE_NAME SYSNAME
    SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = STUFF((
        SELECT CHAR(13) + 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
        ADD
              CreatedUser BIGINT
            , CreatedDate DATETIME
            , ModifiedUser BIGINT
            , ModifiedDate DATETIME
            ,StatusId BIGINT;'
        FROM sys.objects o
        WHERE o.[type] = 'U'
            AND o.[object_id] = OBJECT_ID(@TABLE_NAME)
            AND NOT EXISTS(
                SELECT 1
                FROM sys.columns c
                WHERE o.[object_id] = c.[object_id]
                    AND TYPE_NAME(c.system_type_id) = 'DATETIME'
                    AND c.name IN ('CreatedUser', 'CreatedDate', 'ModifiedUser', 'ModifiedDate','StatusId')
            )
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')

    EXEC sys.sp_executesql @SQL

END

GO

Find all Stored Procedure names which uses a certain column name

Have you ever gone through a situation where, one of your table columns getting updated without you knowing and you need to find which SP does this !!!

Here's the easiest way to find all the references or SP names which uses the table column name that your worried about.

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + '<Column Name>' + '%'
AND TYPE = 'P'

The result will be as follows,

Case Sensitive SQL Query Search

Have you ever noticed when you do a string comparison in SQL using '=' notation, case of the string is neglected ???

For example, If TestTable has a TestColumn and it consist of  values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

If you dont want to ignore case and return only the exact row with the specified value, the query should be modified as follows,

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

The above change should be done since the default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

Background Worker - Complete Explanation

As requested by a commenter/follower of the blog, I created an example which explains all most all the processes related to the Background worker in .Net. The processes from A - Z is explained as follows,

• Add a Background worker to the project

To add a background worker to the form you need, drag & drop the component from the Toolbox under the Components section.

• Allow to Report Current progress or Cancel the background operation

Set the WorkerReportsProgress and WorkerSupportsCancellation properties to "True" in-order to allow the Background worker to allow Report current progress or Cancel the current operation.

Use of Background Worker, Error Provider

Have you ever experienced "Not Responding" message in your software applications, when you start some process ??? What do you think the reason for it ??? It's simply because the .NET Framework, all interaction with the user interface (UI) must be performed on a single thread, often referred to as the UI thread. The UI thread is the only thread that can update the UI or respond to events that the UI raises. So when a busy process is running the UI keeps freezing until the current process completes. The best solution to avoid this situation is to use a Background Worker.

The BackgroundWorker class enables you to run code on another thread. The BackgroundWorker class includes events that indicate the progress of the thread. A UI can subscribe to these events and update the screen by using the UI thread. This enables you to build a UI that remains responsive and up to date.

For more Info

Download the sample from the  following link and you'll be able to get some knowledge on,

• Simple usage of Background Worker
• Usage of Error Provider in .Net framework
• Simple Linq to Sql
• Some coding standards 

Development Environment : MS Visual studio 2010, Sql Server 2005 or above

Download :  StudentManagementSystemExample

NOTE :  Before running the sample application modify the "app.config" file in the "StudentManagementSystem.Service" project with the connection string of your database.

Abstract - Key things about Abstract Classes/Methods

Abstract Classes

• An Abstract class cannot be instantiated.

abstract class SampleAbs

{

...

}

SampleAbs instance = new SampleAbs(); // Compile Error

• It cannot be modified with the 'sealed' modifier. In other words, an Abstract Class should be inherited.
• An Abstract class may contain abstract as well as non abstract members.

Abstract Methods

• An Abstract method cannot contain a method body.

abstract class AbsClass

{

abstract void AbsMethod(); // Legal

abstract void AbsMethod()

{

... // Illegal

}

}

• An abstract method is implicitly a virtual method.
• An Abstract method can only be added in an Abstract Class.

Static - Key things about using 'static'

In this post I'll pin point you the important facts that you should be aware of when using static methods, variable &amp; classes etc.

• If a there is a static field declared in a non static context (class) it can be accessed without creating an object (object of the class).

    class Sales

    {

       public static double salesTaxPercentage = 20;

    }

    Sales.salesTaxPercentage  = 35; // can access like this

' ref ' keyword - Passing value types by reference

The ref method parameter keyword on a method parameter causes a method to refer to the same variable that was passed into the method.  In other words if you do a certain changed to the parameter in the method it'l affect the originally passed arguement as well. To use a ref parameter, the argument must explicitly be passed to the method as a ref argument. 

The following example will explain the above mentioned facts.

 private static void TestMethod(ref int a)
{
    // at this point the original value will be modified 
    a = a * a;
}
static void Main(string[] args)
{
    int a = 10; // First 'a ' is initialized to 10
    TestMethod (ref a); // Invoking and passing ref arguement
    Console.WriteLine(a);
    Console.ReadLine();
    // Output a = 100;
    // If it was not passed by reference the output would be 10      
}

Data Validation in WPF

When it comes to Data Validation most of us use the old mechanism of inspecting the key press. What can can you do If you want the user to enter only values in a specified range for a text box ??? One of the old mechanisms are to check the entered value at a button click event to save the record. Thanks to WPF Validation rules, we can make the work more easier and create cleaner code. The steps of implementing Validation rules will be further explained using an example where you need to validate a Person's age as for the following conditions,

- Age > Min age

- Age < Max age

- Age should only be numeric.

Follow the below steps to implement.

1) Create a WPF project and Add a class (I've used 'AgeRangeValidation' as the name).

2) Bring System.Windows.Controls namespace into scope.

3) Inherit from the ValidationRule class and implement the abstract method Validate

4) Add Automatic properties to set the Min and Max ages.

5) Add code as follows to validate for non numeric characters and age against the specified range.