Sandun's blog

Query database triggers for a text

2012-01-30T12:10:00.000+05:30

Today I wanted to find the database triggers that contains a specific text in it. By referring to my previous post about query stored procedures for a specific text and doing some web search I've been able to build a query as below;

SELECT A.[name], M.[definition]

FROM SYS.SYSOBJECTS A

INNER JOIN SYS.SQL_MODULES M ON A.[id] = M.[object_id]

WHERE A.[type] = 'TR'

AND M.[definition] LIKE '%delete%'

PS: Thanks for Malmi :)

C# Static Field Initialisation

2012-01-29T11:26:00.001+05:30

Static Fields

Fields can be declared as static in the same manner as methods and properties. The addition of the static keyword indicates that a field is accessed using its class name, rather than via an instance of the class. When you create a class that contains static fields, it is important that you initialize them in the correct order. Consider the following code:

class AttemptController

{

static int Threshold = MaxAttempts - WarningAttempts;

static int MaxAttempts = 5;

static int WarningAttempts = 2;

}

At a glance the above code looks OK. The MaxAttempts value is set to 5, the WarningAttempts field to 2 and the Threshold value to the difference of the other two fields. You might expect the Threshold value to be 3. However, if you run the following code to output the values, you will see results that you may not expect:

Console.WriteLine("Maximum: {0}", AttemptController.MaxAttempts);

Console.WriteLine("Warning: {0}", AttemptController.WarningAttempts);

Console.WriteLine("Threshold: {0}", AttemptController.Threshold);

/* OUTPUT

Maximum: 5

Warning: 2

Threshold: 0

As you can see in the comment, it appears that the Threshold field's value is being incorrectly calculated.

C# Specification

The cause of the unexpected value can be found in the C# Language Specification.

The Threshold field cannot be evaluated until MaxAttempts and WarningAttempts being correctly initialise. If the initialisation is fail to evaluate, then the default value of its type will return. For integer values, this is zero.

The C# Language Specification also tells us that when static fields are initialised by applying a value in their declaration, as we have done above, they are set in the order in which they appear in the code. This means that when the Threshold value is calculated, we are using two uninitialised values, each of which yields a result of zero. The Threshold field's calculation is therefore the result of zero minus zero, which is zero.

A simple fix is to reorder the declarations to ensure that the MaxAttempts and WarningAttempts values are present before they are needed, as shown below:

class AttemptController

{

static int MaxAttempts = 5;

static int WarningAttempts = 2;

static int Threshold = MaxAttempts - WarningAttempts;

}

Running the program now gives the correct results:

Maximum: 5

Warning: 2

Threshold: 3

But this approach does not generate the most readable and maintainable code. It is quite possible that another developer may change the order of the fields at a later time, may be to sort them alphabetically, unknowingly introducing a bug. A better fix is to remove the initialisation from the field declarations and instead set the values within a static constructor, as shown below:

class AttemptController

{

static int MaxAttempts;

static int WarningAttempts;

static int Threshold;

static AttemptController()

{

MaxAttempts = 5;

WarningAttempts = 2;

Threshold = MaxAttempts - WarningAttempts;

}

reference: http://www.blackwasp.co.uk/StaticFieldInitOrder.aspx

Sorting an alphanumeric column in a DataTable

2011-11-08T12:21:00.001+05:30

I'm getting a DataTable via a database table view where it contains a varchar column. For example purpose we'll call the column as "UnitNumber" of a home address. UnitNumber most of the time contains only a number, but there can be letters as well.

My requirement is to sort the DataTable by UnitNumber column, but normal sorting will give a result like below;

17B

26A

3
32

This order is incorrect. In stored procedure level we can use REPLICATE() function to solve this problem, but I'm in CSharp code now.

The correct sorted order should be as below;

1
2

3
10

17B
26A

The below function will accept a data table to be sorted and the sorting column name as string.(In this example I consider the column contains only string values and integer values only.)

/// <summary>

/// Sort a data table by an alphanumeric column

/// </summary>

/// <param name="dtUnsorted">Data table to be sorted</param>

/// <param name="columnName">The alphanumeric column name</param>

/// <returns>Sorted data table</returns>

public static DataTable SortByAlphanumericColumn(DataTable dtUnsorted, string columnName)

{

string tempColumnName = columnName + "INT";

// Add a temp column to hold the numberic columnName values

dtUnsorted.Columns.Add(new DataColumn(tempColumnName, typeof(int)));

// Loop the data table and copy the numberic unit number values into tempColumnName column

foreach (DataRow row in dtUnsorted.Rows)

{

int i;

if (int.TryParse(row[columnName].ToString(), out i))

{

row[tempColumnName] = i;

}

// Sort by tempColumnName and then columnName column (Numberic records come first and string records will come next)

dtUnsorted.DefaultView.Sort = string.Format("{0} ASC, {1} ASC",tempColumnName, columnName);

DataTable dtSorted = dtUnsorted.DefaultView.ToTable(dtUnsorted.TableName);

dtUnsorted.Dispose();

// Remove the temp column

dtSorted.Columns.Remove(tempColumnName);

dtSorted.AcceptChanges();

return dtSorted;

}

Restore the backup in Xperia x10 after Gingerbread (2.3.3) upgrade

2011-08-13T17:58:00.002+05:30

Today I've been able to upgrade my Sony Ericsson Xperia x10 phone from Android OS 2.1 to 2.3.3 as usual after taking a backup of all my contacts, apps, messages, etc. But after the upgrade there is no Backup & Restore application in the system that was in the 2.1 OS. As they have mentioned I tried MyBackup application to do the restoration, but it didn't able to read the sbf file. So I have no way of taking back my contacts from that sbf binary file.

While searching on the net I found few forums having the old Backup & Restoring application shared as a solution for my situation (while blaming on SE not to include the application).

If you also in the same situation, I have shared those sites for further information.

http://androidforums.com/sony-ericsson-xperia-x10/385596-gingerbread-backup-whoopsy.html

http://forum.xda-developers.com/showthread.php?p=16216185

Disabling MSSQL Server Table Constraints

2011-03-18T18:40:00.002+05:30

SQL Server databases should be configured with foreign key constraints to maintain referential integrity and check constraints to ensure that table data is always valid. Occasionally these constraints can be a hindrance and may be temporarily disabled.

Constraints

The integrity of a MSSQL Server database is ensured with the correct application of constraints, such as foreign keys, unique keys and check constraints. With these in place, any attempt to insert invalid data into a table is refused with an error report indicating which constraint prevented the action.

In some situations you may wish to insert data that would break the integrity of the database, knowing that further actions that you are about to take will remedy the problem. For example, you may be performing bulk operations on related tables that cause foreign key constraints to be breached. Perhaps you could avoid errors by processing rows individually and ensuring that changes in one table are reflected in related tables immediately. However, this may be a much slower process than performing bulk actions on one table at a time. In such situations you can temporarily disable individual constraints or all rules for a table.

NB: The commands described in this article should be used with care. With constraints disabled it is possible to create invalid data. These techniques should be used for maintenance of data only and not as part of a business process. You should consider using single user mode to ensure other users cannot damage data,

Disabling All Constraints on a Table

When there are several constraints on a table you can disable them all with one statement. To do so, use the ALTER TABLE command, providing the name of the table and the clause, NOCHECK CONSTRAINT ALL. For example, the following disables all of the constraints for the CustomerAddresses table:

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT ALL

To re-enable the constraints, issue uses the same command, substituting NOCHECK with CHECK, as follows:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT ALL

Disabling Individual Constraints

Sometimes you will wish to disable one constraint only. You can use the same ALTER TABLE command but instead of specifying ALL, provide the name of the constraint that you wish to remove. The following disables the FK_CustomerAddresses_Customers constraint on the CustomerAddresses table.

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT FK_CustomerAddresses_Customers

To re-enable the constraint change NOCHECK to CHECK:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT FK_CustomerAddresses_Customers

source : http://www.blackwasp.co.uk/SQLDisableConstraints.aspx

related articles : http://www.blackwasp.co.uk/SQLDisableTriggers.aspx

Cannot start Microsoft Outlook. Cannot open the Outlook window.

2011-01-14T11:18:00.000+05:30

When trying to start Outlook 2007/2010 if you get the following error;

“Cannot start Microsoft Outlook. Cannot open the Outlook window.”

How can you recover and start Outlook again?

While there are various reasons for this to occur, some post Office updates seems to cause this error for certain configurations.

In these instances, the configuration file for the Navigation Pane gets corrupted and prevents Outlook from starting successfully. To recover, you can start Outlook with the resetnavpane switch;

Windows Vista and Windows 7

Start-> type; outlook.exe /resetnavpane

Windows XP

Start-> Run; outlook.exe /resetnavpane

Please note the space between outlook.exe and /resetnavpane.

While the above method currently applies to most cases experiencing this issue, there could be other causes for this to happen as well. If you are still experiencing the issue after applying this fix you can try these troubleshooting steps or post in the Outlook newsgroups.

Note:

/resetnavpane
Clears and regenerates the Navigation Pane for the current profile.

References:

Show images from database binary content via memory stream

2010-12-15T11:22:00.003+05:30

Most of the time when we upload a file, specially image files what we did was save the file in a folder and keep the file path in the database. And when reading we refer to the path that we have saved in the database. Nowadays this approach is a bit older fashion and difficult to manage the application in taking backups and other deployment issues.

The other option is to directly store the file's binary content in the database in a binary/image type field. Now the point is how to display the image in a web page without creating a physical image file in a folder.

First I'll create a web page to display multiple images in a GridView control as below,

Markup code:

<body>

<div>

<asp:GridView ID="GridView1" runat="server" ShowHeader="false" AutoGenerateColumns="False"

OnRowDataBound="GridView1_RowDataBound">

<asp:TemplateField>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

</form>

</body>

Code behind:

public partial class ShowImages : System.Web.UI.Page

{

readonly string connectionString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

LoadImages();

}

private void LoadImages()

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand command = connection.CreateCommand();

command.CommandType = CommandType.Text;

command.CommandText = "SELECT * FROM ImagesTable";

connection.Open();

GridView1.DataSource = command.ExecuteReader();

GridView1.DataBind();

}

protected void GridView1_RowDataBound(object sender, System.Web.UI.WebControls.GridViewRowEventArgs e)

{

if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)

{

System.Web.UI.HtmlControls.HtmlImage img = e.Row.FindControl("Image1") as System.Web.UI.HtmlControls.HtmlImage;

img.Src = string.Format("Image.ashx?id={0}", DataBinder.Eval(e.Row.DataItem, "ID"));

}

Now you should have a question about what is this Images.ashx file which is not a web page. Yes it is not a web page but a Generic Handler class which can handle a Http request and return a customized Http response

Here is the code in Image.ashx file:

public class Image1 : IHttpHandler

{

readonly string connectionString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

public bool IsReusable

{

get

{

return false;

}

public void ProcessRequest(HttpContext context)

{

int id = Int32.Parse(context.Request.QueryString["id"]);

if (id != 0)

{

object fileContent;

byte[] byteArray;

using (SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

SqlCommand command = connection.CreateCommand();

command.CommandType = CommandType.Text;

command.CommandText = string.Format("SELECT ImageContent FROM ImagesTable WHERE ID = {0}", id);

fileContent = command.ExecuteScalar();

}

if (fileContent != null)

{

byteArray = (byte[])fileContent;

System.Drawing.Image img = GetImage(byteArray);

HttpContext.Current.Response.ContentType = "image/jpeg";

img.Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);

}

private System.Drawing.Image GetImage(byte[] byteArray)

{

MemoryStream stream = new MemoryStream(byteArray);

return System.Drawing.Image.FromStream(stream);

}

In the Image.ashx file the Image object will be created using the binary content in the database and save it to the OutputStream in the Http response object. So the HtmlImage control in the template field of the GridView control receive an Image object to be shown.

Note: To save the disk space, most of the time when we storing the file in the database, we compress the binary content before send to the database, so remember to decompress them before creating the MemoryStream object.

Conclusion: We can do the same thing using another web page instead of a Generic Handler class, but using a Generic Handler class is more light weight and more efficient.

DateTime formatting in MSSQL Server

2010-12-14T11:01:00.000+05:30

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime 

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

                                        – Oct  2 2008 11:01AM          

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008                  

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        – Oct  2 2008 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

                                        – 02 Oct 2008 11:02:07:577     

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

                                        – 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

————

– SQL Server date formatting function – convert datetime to string

————

– SQL datetime functions

– SQL Server date formats

– T-SQL convert dates

– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

    DECLARE @StringDate VARCHAR(32)

    SET @StringDate = @FormatMask

    IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

                         DATENAME(YY, @Datetime))

    IF (CHARINDEX (‘YY’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘YY’,

                         RIGHT(DATENAME(YY, @Datetime),2))

    IF (CHARINDEX (‘Month’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘Month’,

                         DATENAME(MM, @Datetime))

    IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

       SET @StringDate = REPLACE(@StringDate, ‘MON’,

                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))

    IF (CHARINDEX (‘Mon’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘Mon’,

                                     LEFT(DATENAME(MM, @Datetime),3))

    IF (CHARINDEX (‘MM’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘MM’,

                  RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

    IF (CHARINDEX (‘M’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘M’,

                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

    IF (CHARINDEX (‘DD’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘DD’,

                         RIGHT(’0′+DATENAME(DD, @Datetime),2))

    IF (CHARINDEX (‘D’,@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, ‘D’,

                                     DATENAME(DD, @Datetime))   

RETURN @StringDate

END

GO

– Microsoft SQL Server date format function test

– MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)           – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)           – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)            – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)             – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)               – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)             – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)         – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)         – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)       – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)           – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)             – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)           – 2012-01-03

– CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’)      – 12.01.03

GO

————

/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

– SQL format datetime

– Default format: Oct 23 2006 10:40AM

SELECT [Default]=CONVERT(varchar,GETDATE(),100)

– US-Style format: 10/23/2006

SELECT [US-Style]=CONVERT(char,GETDATE(),101)

– ANSI format: 2006.10.23

SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

– UK-Style format: 23/10/2006

SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

– German format: 23.10.2006

SELECT [German]=CONVERT(varchar,GETDATE(),104)

– ISO format: 20061023

SELECT ISO=CONVERT(varchar,GETDATE(),112)

– ISO8601 format: 2008-10-23T19:20:16.003

SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

————

– SQL Server datetime formats

– Century date format MM/DD/YYYY usage in a query

– Format dates SQL Server 2005

SELECT TOP (1)

      SalesOrderID,

      OrderDate = CONVERT(char(10), OrderDate, 101),

      OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

/* Result

SalesOrderID      OrderDate               OrderDateTime

43697             07/01/2001          2001-07-01 00:00:00.000

*/

– SQL update datetime column

– SQL datetime DATEADD

UPDATE Production.Product

SET ModifiedDate=DATEADD(dd,1, ModifiedDate)

WHERE ProductID = 1001

– MM/DD/YY date format

– Datetime format sql

SELECT TOP (1)

      SalesOrderID,

      OrderDate = CONVERT(varchar(8), OrderDate, 1),

      OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY SalesOrderID desc

/* Result

SalesOrderID      OrderDate         OrderDateTime

75123             07/31/04          2004-07-31 00:00:00.000

*/

– Combining different style formats for date & time

– Datetime formats

– Datetime formats sql

DECLARE @Date DATETIME

SET @Date = ’2015-12-22 03:51 PM’

SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)

– Result: 12-22-2015  3:51PM

– Microsoft SQL Server cast datetime to string

SELECT stringDateTime=CAST (getdate() as varchar)

– Result: Dec 29 2012  3:47AM

————

– SQL Server date and time functions overview

————

– SQL Server CURRENT_TIMESTAMP function

– SQL Server datetime functions

– local NYC – EST – Eastern Standard Time zone

– SQL DATEADD function – SQL DATEDIFF function

SELECT CURRENT_TIMESTAMP                        – 2012-01-05 07:02:10.577

– SQL Server DATEADD function

SELECT DATEADD(month,2,’2012-12-09′)            – 2013-02-09 00:00:00.000

– SQL Server DATEDIFF function

SELECT DATEDIFF(day,’2012-12-09′,’2013-02-09′)  – 62

– SQL Server DATENAME function

SELECT DATENAME(month,   ’2012-12-09′)          – December

SELECT DATENAME(weekday, ’2012-12-09′)          – Sunday

– SQL Server DATEPART function

SELECT DATEPART(month, ’2012-12-09′)            – 12

– SQL Server DAY function

SELECT DAY(’2012-12-09′)                        – 9

– SQL Server GETDATE function

– local NYC – EST – Eastern Standard Time zone

SELECT GETDATE()                                – 2012-01-05 07:02:10.577

– SQL Server GETUTCDATE function

– London – Greenwich Mean Time

SELECT GETUTCDATE()                             – 2012-01-05 12:02:10.577

– SQL Server MONTH function

SELECT MONTH(’2012-12-09′)                      – 12

– SQL Server YEAR function

SELECT YEAR(’2012-12-09′)                       – 2012

————

– T-SQL Date and time function application

– CURRENT_TIMESTAMP and getdate() are the same in T-SQL

————

– SQL first day of the month

– SQL first date of the month

– SQL first day of current month – 2012-01-01 00:00:00.000

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of the month

– SQL last date of the month

– SQL last day of current month – 2012-01-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))

– SQL first day of last month

– SQL first day of previous month – 2011-12-01 00:00:00.000

SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of last month

– SQL last day of previous month – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))

– SQL first day of next month – 2012-02-01 00:00:00.000

SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

– SQL last day of next month – 2012-02-28 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))

GO

– SQL first day of a month – 2012-10-01 00:00:00.000

DECLARE @Date datetime; SET @Date = ’2012-10-23′

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))

GO

– SQL last day of a month – 2012-03-31 00:00:00.000

DECLARE @Date datetime; SET @Date = ’2012-03-15′

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))

GO

– SQL first day of year 

– SQL first day of the year  -  2012-01-01 00:00:00.000

SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)

– SQL last day of year  

– SQL last day of the year   – 2012-12-31 00:00:00.000

SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,

                     DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))

– SQL last day of last year

– SQL last day of previous year   – 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))

GO

– SQL calculate age in years, months, days

– SQL table-valued function

– SQL user-defined function – UDF

– SQL Server age calculation – date difference

– Format dates SQL Server 2008

USE AdventureWorks2008;

GO

CREATE FUNCTION fnAge  (@BirthDate DATETIME)

RETURNS @Age TABLE(Years  INT,

                   Months INT,

                   Days   INT)

AS

  BEGIN

    DECLARE  @EndDate     DATETIME, @Anniversary DATETIME

    SET @EndDate = Getdate()

    SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)

    INSERT @Age

    SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE

                                                 WHEN @Anniversary > @EndDate THEN 1

                                                 ELSE 0

END), 0, 0

     UPDATE @Age     SET    Months = Month(@EndDate - @Anniversary) - 1

    UPDATE @Age     SET    Days = Day(@EndDate - @Anniversary) - 1

    RETURN

END

GO

– Test table-valued UDF

SELECT * FROM   fnAge(’1956-10-23′)

SELECT * FROM   dbo.fnAge(’1956-10-23′)

/* Results

Years       Months      Days

52          4           1

*/

———-

– SQL date range between

———-

– SQL between dates

USE AdventureWorks;

– SQL between

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN ’20040301′ AND ’20040315′

– Result: 108

– BETWEEN operator is equivalent to >=…AND….<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN ’2004-03-01 00:00:00.000′ AND ’2004-03-15  00:00:00.000′

/*

Orders with OrderDates

’2004-03-15  00:00:01.000′  – 1 second after midnight (12:00AM)

’2004-03-15  00:01:00.000′  – 1 minute after midnight

’2004-03-15  01:00:00.000′  – 1 hour after midnight

are not included in the two queries above.

*/

– To include the entire day of 2004-03-15 use the following two solutions

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= ’20040301′ AND OrderDate < ’20040316′

– SQL between with DATE type (SQL Server 2008)

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE CONVERT(DATE, OrderDate) BETWEEN ’20040301′ AND ’20040315′

———-

– Non-standard format conversion: 2011 December 14

– SQL datetime to string

SELECT [YYYY Month DD] =

CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+

DATENAME(MM, GETDATE()) + ‘ ‘ +

CAST(DAY(GETDATE()) AS VARCHAR(2))

– Converting datetime to YYYYMMDDHHMMSS format: 20121214172638

SELECT replace(convert(varchar, getdate(),111),‘/’,”) +

replace(convert(varchar, getdate(),108),‘:’,”)

– Datetime custom format conversion to YYYY_MM_DD

select CurrentDate=rtrim(year(getdate())) + ‘_’ +

right(’0′ + rtrim(month(getdate())),2) + ‘_’ +

right(’0′ + rtrim(day(getdate())),2)

– Converting seconds to HH:MM:SS format

declare @Seconds int

set @Seconds = 10000

select TimeSpan=right(’0′ +rtrim(@Seconds / 3600),2) + ‘:’ +

right(’0′ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +

right(’0′ + rtrim(@Seconds % 60),2)

– Result: 02:46:40

– Test result

select 2*3600 + 46*60 + 40

– Result: 10000

– Set the time portion of a datetime value to 00:00:00.000

– SQL strip time from date

– SQL strip time from datetime

SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)

– Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000

/*******

VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

SMALLDATETIME date range:

January 1, 1900 through June 6, 2079

DATETIME date range:

January 1, 1753 through December 31, 9999

DATETIME2 date range (SQL Server 2008):

January 1,1 AD through December 31, 9999 AD

DATE date range (SQL Server 2008):

January 1, 1 AD through December 31, 9999 AD

*******/

– Selecting with CONVERT into different styles

– Note: Only Japan & ISO styles can be used in ORDER BY

SELECT TOP(1)

     Italy  = CONVERT(varchar, OrderDate, 105)

   , USA    = CONVERT(varchar, OrderDate, 110)

   , Japan  = CONVERT(varchar, OrderDate, 111)

   , ISO    = CONVERT(varchar, OrderDate, 112)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY PurchaseOrderID DESC

/* Results

Italy       USA         Japan       ISO

25-07-2004  07-25-2004  2004/07/25  20040725

*/

– SQL Server convert date to integer

DECLARE @Datetime datetime

SET @Datetime = ’2012-10-23 10:21:05.345′

SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)

– Result: 20121023

– SQL Server convert integer to datetime

DECLARE @intDate int

SET @intDate = 20120315

SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)

– Result: 2012-03-15 00:00:00.000

————

– SQL Server CONVERT script applying table INSERT/UPDATE

————

– SQL Server convert date

– Datetime column is converted into date only string column

USE tempdb;

GO

CREATE TABLE sqlConvertDateTime   (

            DatetimeCol datetime,

            DateCol char(8));

INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

UPDATE sqlConvertDateTime

SET DateCol = CONVERT(char(10), DatetimeCol, 112)

SELECT * FROM sqlConvertDateTime

– SQL Server convert datetime

– The string date column is converted into datetime column

UPDATE sqlConvertDateTime

SET DatetimeCol = CONVERT(Datetime, DateCol, 112)

SELECT * FROM sqlConvertDateTime

– Adding a day to the converted datetime column with DATEADD

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))

SELECT * FROM sqlConvertDateTime

– Equivalent formulation

– SQL Server cast datetime

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))

SELECT * FROM sqlConvertDateTime

GO

DROP TABLE sqlConvertDateTime

GO

/* First results

DatetimeCol                   DateCol

2014-12-25 16:04:15.373       20141225 */

/* Second results:

DatetimeCol                   DateCol

2014-12-25 00:00:00.000       20141225  */

/* Third results:

DatetimeCol                   DateCol

2014-12-26 00:00:00.000       20141225  */

————

– SQL month sequence – SQL date sequence generation with table variable

– SQL Server cast string to datetime – SQL Server cast datetime to string

– SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

DECLARE @StartDate datetime;

SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+

                 RIGHT(’0′+convert(varchar,month(getdate())),2) + ’01′ AS DATETIME)

WHILE ( @i < 120)

BEGIN

      INSERT @Sequence DEFAULT VALUES

      SET @i = @i + 1

END

SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)

FROM @Sequence

GO

/* Partial results:

MonthSequence

Jan  1 2012 12:00AM

Feb  1 2012 12:00AM

Mar  1 2012 12:00AM

Apr  1 2012 12:00AM

*/

————

————

– SQL Server Server datetime internal storage

– SQL Server datetime formats

————

– SQL Server datetime to hex

SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))

/* Results

Now                     HexNow

2009-01-02 17:35:59.297 0x00009B850122092D

*/

– SQL Server date part – left 4 bytes – Days since 1900-01-01

SELECT Now=DATEADD(DAY, CONVERT(INT, 0x00009B85), ’19000101′)

GO

– Result: 2009-01-02 00:00:00.000

– SQL time part – right 4 bytes – milliseconds since midnight

– 1000/300 is an adjustment factor

– SQL dateadd to Midnight

SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0x0122092D), ’2009-01-02′)

GO

– Result: 2009-01-02 17:35:59.290

————

————

– String date and datetime date&time columns usage

– SQL Server datetime formats in tables

————

USE tempdb;

SET NOCOUNT ON;

– SQL Server select into table create

SELECT TOP (5)

      FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),

      BirthDate = CONVERT(char(8), BirthDate,112),

      ModifiedDate = getdate()

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.Person.Contact c

ON c.ContactID = e.ContactID

ORDER BY EmployeeID

GO

– SQL Server alter table

ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL

GO

ALTER TABLE Employee

ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )

GO

/* Results

Table definition for the Employee table

Note: BirthDate is string date (only)

CREATE TABLE dbo.Employee(

      FullName nvarchar(50) NOT NULL PRIMARY KEY,

      BirthDate char(8) NULL,

      ModifiedDate datetime NOT NULL

)

*/

SELECT * FROM Employee ORDER BY FullName

GO

/* Results

FullName                BirthDate   ModifiedDate

Guy Gilbert             19720515    2009-01-03 10:10:19.217

Kevin Brown             19770603    2009-01-03 10:10:19.217

Rob Walters             19650123    2009-01-03 10:10:19.217

Roberto Tamburello      19641213    2009-01-03 10:10:19.217

Thierry D’Hers          19490829    2009-01-03 10:10:19.217

*/

– SQL Server age

SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),

       RowMaintenanceDate = CAST (ModifiedDate AS varchar)

FROM Employee ORDER BY FullName

GO

/* Results

FullName                Age   RowMaintenanceDate

Guy Gilbert             37    Jan  3 2009 10:10AM

Kevin Brown             32    Jan  3 2009 10:10AM

Rob Walters             44    Jan  3 2009 10:10AM

Roberto Tamburello      45    Jan  3 2009 10:10AM

Thierry D’Hers          60    Jan  3 2009 10:10AM

*/

– SQL Server age of Rob Walters on specific dates

– SQL Server string to datetime implicit conversion with DATEADD

SELECT AGE50DATE = DATEADD(YY, 50, ’19650123′)

GO

– Result: 2015-01-23 00:00:00.000

– SQL Server datetime to string, Italian format for ModifiedDate

– SQL Server string to datetime implicit conversion with DATEDIFF

SELECT FullName,

         AgeDEC31 = DATEDIFF(YEAR, BirthDate, ’20141231′),

         AgeJAN01 = DATEDIFF(YEAR, BirthDate, ’20150101′),

         AgeJAN23 = DATEDIFF(YEAR, BirthDate, ’20150123′),

         AgeJAN24 = DATEDIFF(YEAR, BirthDate, ’20150124′),

       ModDate = CONVERT(varchar, ModifiedDate, 105)

FROM Employee

WHERE FullName = ‘Rob Walters’

ORDER BY FullName

GO

/* Results

Important Note: age increments on Jan 1 (not as commonly calculated)

FullName    AgeDEC31    AgeJAN01    AgeJAN23    AgeJAN24    ModDate

Rob Walters 49          50          50          50          03-01-2009

*/

————

– SQL combine integer date & time into datetime

————

– Datetime format sql

– SQL stuff

DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key, 

   DateAsINT int, 

   TimeAsINT int 

)

– NOTE: leading zeroes in time is for readability only!  

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)  

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)  

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)  

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)  

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)  

SELECT DateAsINT, TimeAsINT,

  CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+

  STUFF(STUFF ( RIGHT(REPLICATE(’0′, 6) + CONVERT(varchar(6), TimeAsINT), 6),

                  3, 0, ‘:’), 6, 0, ‘:’))  AS DateTimeValue

FROM   @DateTimeAsINT 

ORDER BY ID

GO

/* Results

DateAsINT   TimeAsINT   DateTimeValue

20121023    235959      2012-10-23 23:59:59.000

20121023    10204       2012-10-23 01:02:04.000

20121023    2350        2012-10-23 00:23:50.000

20121023    244         2012-10-23 00:02:44.000

20121023    50          2012-10-23 00:00:50.000

20121023    6           2012-10-23 00:00:06.000

*/

————

– SQL Server string to datetime, implicit conversion with assignment

UPDATE Employee SET ModifiedDate = ’20150123′

WHERE FullName = ‘Rob Walters’

GO

SELECT ModifiedDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 2015-01-23 00:00:00.000

/* SQL string date, assemble string date from datetime parts  */

– SQL Server cast string to datetime – sql convert string date

– SQL Server number to varchar conversion

– SQL Server leading zeroes for month and day

– SQL Server right string function

UPDATE Employee SET BirthDate =

      CONVERT(char(4),YEAR(CAST(’1965-01-23′ as DATETIME)))+

      RIGHT(’0′+CONVERT(varchar,MONTH(CAST(’1965-01-23′ as DATETIME))),2)+

      RIGHT(’0′+CONVERT(varchar,DAY(CAST(’1965-01-23′ as DATETIME))),2)

      WHERE FullName = ‘Rob Walters’

GO

SELECT BirthDate FROM Employee WHERE FullName = ‘Rob Walters’

GO

– Result: 19650123

– Perform cleanup action

DROP TABLE Employee

– SQL nocount

SET NOCOUNT OFF;

GO

————

————

– sql isdate function

————

USE tempdb;

– sql newid – random sort

SELECT top(3) SalesOrderID,

stringOrderDate = CAST (OrderDate AS varchar)

INTO DateValidation

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY NEWID()

GO

SELECT * FROM DateValidation

/* Results

SalesOrderID      stringOrderDate

56720             Oct 26 2003 12:00AM

73737             Jun 25 2004 12:00AM

70573             May 14 2004 12:00AM

*/

– SQL update with top

UPDATE TOP(1) DateValidation

SET stringOrderDate = ‘Apb 29 2004 12:00AM’

GO

– SQL string to datetime fails without validation

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

GO

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an

out-of-range value.

*/

– sql isdate – filter for valid dates

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

WHERE ISDATE(stringOrderDate) = 1

GO

/* Results

SalesOrderID      OrderDate

73737             2004-06-25 00:00:00.000

70573             2004-05-14 00:00:00.000

*/

– SQL drop table

DROP TABLE DateValidation

Go

————

– SELECT between two specified dates – assumption TIME part is 00:00:00.000

————

– SQL datetime between

– SQL select between two dates

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ’1997-11-01′ AND 

      RateChangeDate < DATEADD(dd,1,’1998-01-05′)

GO

/* Results

EmployeeID  RateChangeDate

3           1997-12-12 00:00:00.000

4           1998-01-05 00:00:00.000

*/

/* Equivalent to

– SQL datetime range

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= ’1997-11-01 00:00:00′ AND 

      RateChangeDate <  ’1998-01-06 00:00:00′

GO

*/

————

– SQL datetime language setting

– SQL Nondeterministic function usage – result varies with language settings

SET LANGUAGE  ‘us_english’;  –– Jan 12 2015 12:00AM 

SELECT US = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘British’;     –– Dec  1 2015 12:00AM 

SELECT UK = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘German’;      –– Dez  1 2015 12:00AM 

SET LANGUAGE  ‘Deutsch’;     –– Dez  1 2015 12:00AM 

SELECT Germany = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘French’;      –– déc  1 2015 12:00AM 

SELECT France = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘Spanish’;     –– Dic  1 2015 12:00AM 

SELECT Spain = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘Hungarian’;   –– jan 12 2015 12:00AM 

SELECT Hungary = convert(VARCHAR,convert(DATETIME,’01/12/2015′));

SET LANGUAGE  ‘us_english’;

GO

————

————

– Function for Monday dates calculation

————

USE AdventureWorks2008;

GO

– SQL user-defined function

– SQL scalar function – UDF

CREATE FUNCTION fnMondayDate

               (@Year          INT,

                @Month         INT,

                @MondayOrdinal INT)

RETURNS DATETIME

AS

  BEGIN

    DECLARE  @FirstDayOfMonth CHAR(10),

             @SeedDate        CHAR(10)

    SET @FirstDayOfMonth = convert(VARCHAR,@Year) + ‘-’ + convert(VARCHAR,@Month) + ‘-01′

    SET @SeedDate = ’1900-01-01′

    RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,

                  @FirstDayOfMonth)) / 7 * 7,  @SeedDate)

END

GO

– Test Datetime UDF

– Third Monday in Feb, 2015

SELECT dbo.fnMondayDate(2016,2,3)

– 2015-02-16 00:00:00.000

– First Monday of current month

SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)

– 2009-02-02 00:00:00.000  

————

reference <http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/>

FileUpload inside UpdatePanels

2010-12-06T17:21:00.000+05:30

In a recent project I was involve in creating a user control that includes a File Upload control. The file uploading is working fine unless the File Upload control will not wrapped by an Update Panel. Once the File Upload control got into an Update Panel it is unable to send the file to the server. But there is no proper way to avoid getting wrapped with an Update Panel.

The reason that a File Upload will not work with in an Update Panel is because the File Upload control requires a full post back in order to work (as Update Panel does asynchronous post backs).

This can be solved by adding the submit button to the File Upload control to the post back trigger collection of the Update Panel as below shown.

<asp:ToolkitScriptManager ID=" Main_ToolkitScriptManager" runat="server" />

<asp:UpdatePanel ID="upnl" runat="server">

<asp:FileUpload ID="fileUploader" runat="server" />

<asp:Button ID="SubmitButton" runat="server" />

</ContentTemplate>

<asp:PostBackTrigger ControlID="SubmitButton" />

</Triggers>

</asp:UpdatePanel>

Note: Earlier version uses ScriptManager and AJAX toolkit for .NET 4.0 uses ToolkitScriptManager instead.

However this will work for a basic page, but most of the application nowadays uses master pages. So the script manager is placed in master page and the update panel is in child page. In such a scenario we have to add the submit button to the post back trigger collection to the master pages script manager. To do that we can expose a public method inside the master page’s code behind as shown below;

public void AddPostbackTrigger(Control triggerTo)

{

Main_ToolkitScriptManager.RegisterPostBackControl(triggerTo);

}

In the user control where the file upload control is have to add the below statement in Page_OnInit() or Page_PreRender() events in order to register the post back trigger to the script manager.

((SiteMaster)this.Page.Master).AddPostbackTrigger(SubmitButton);

Now the submit button will cause a full post back, that make the File Upload to work as expected.

.NET 4.0 GC enhancement in UnitTest

2010-10-15T17:36:00.000+05:30

This feature (or bug) I found when writing a UnitTest class. As the below statement shows I'm accepting an integer from the calling method, and the method is returning a value(I debugged it seperately). But since the assigned variable (primaryKey) is not used in any other below statements I'm not able to see the value of the variable in debug mode. When I consume that variable in a below statement now it is viewable as normal.

This situation could be a .NET 4.0 feature or a feature for UnitTest projects (or a bug). The Garbage Collector should be more intelligent to do this cleaning work that .NET 3.5 version.

// variable declaration statement.
int primaryKey = target.UpdateTable(insertTable, keyFieldName);

// newly added statement to consume the primaryKey variable.
Assert.IsTrue(primaryKey > 0);

How to add few common columns to a set of tables

2010-10-07T14:53:00.000+05:30

I was involve in creating a new database for a new project. After normalizing the database tables we have created the tables. Now some new common set of columns needed to be added to every table created. Doing this for each and every table is the normal way, but it is more vulnerable to mistakes like spellings.

We've been able to write a SQL cursor to do this work for us.


DECLARE alter_table_cursor CURSOR FOR 
 (SELECT TABLE_CATALOG + '.' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']'
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE')

OPEN alter_table_cursor

DECLARE @tableName SYSNAME

FETCH NEXT FROM alter_table_cursor INTO @tableName

WHILE (@@FETCH_STATUS = 0)
BEGIN
 -- Good to print and see what are the queries that will be executed before them actually executed
 PRINT('ALTER TABLE ' + @tableName + ' ADD CreatedBy INT NULL, CreatedDate DATETIME NULL, LastUpdatedBy INT NULL, LastUpdatedDate DATETIME NULL')
 --EXEC('ALTER TABLE ' + @tableName + ' ADD CreatedBy INT NULL, CreatedDate DATETIME NULL, LastUpdatedBy INT NULL, LastUpdatedDate DATETIME NULL')

 FETCH NEXT FROM alter_table_cursor INTO @tableName
END

CLOSE alter_table_cursor
DEALLOCATE alter_table_cursor

To remove some common set of columns from tables just replace the "ADD" with "DROP" and remove data types in the statement with in the "WHILE" loop.