How to use the undocumented stored procedure sp_msforeachtable to add a column to all tables in a database ?

There are two examples, in both I add a column to each tables except the spnet membership tables.

Example :

Add the “CreatedDate” column to all tables :



print 'Add [CreatedDate] Column for each user table'

EXEC sp_msforeachtable
 '
-- add column
exec(''
 declare @tableName as nvarchar(max)
 set @tableName = ''''_?_''''

 IF (CHARINDEX(''''aspnet_'''',@tableName) = 0) -- no aspnet membership
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''?'''') AND type in (N''''U''''))
 BEGIN
 IF columnproperty(object_id(''''?''''), ''''CreatedDate'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedDate] datetime NOT NULL
 END
 END
 END
'')

' ;
print ' [CreatedDate] Column is created for each user table'

Example 2 :

Add the “CreatedUserID” column to all tables and enable constraints. For the example, the reference use the aspnet_Users table

print 'Add [CreatedUserID] Column for each user table'

EXEC sp_msforeachtable
 '
-- add column
exec(''

 declare @tableName as nvarchar(max)
 set @tableName = ''''_?_''''

 IF (CHARINDEX(''''aspnet_'''',@tableName) = 0) -- no aspnet membership
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''?'''') AND type in (N''''U''''))
 BEGIN
 IF columnproperty(object_id(''''?''''), ''''CreatedUserID'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedUserID] uniqueidentifier NOT NULL
 END
 END
 END
'')
-- add constraint

 declare @constraintName as nvarchar(max)
 set @constraintName = ''FK_?_CreatedUserID''
 IF (CHARINDEX(''aspnet_'',@constraintName) = 0) -- no aspnet membership
 BEGIN
 set @constraintName = REPLACE(@constraintName,''[dbo].['','''')
 set @constraintName = REPLACE(@constraintName,'']'','''')
 IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''''+@constraintName+'''') AND type = (N''F''))
 BEGIN
 exec(''
 ALTER TABLE ? ADD CONSTRAINT [''+@constraintName+''] FOREIGN KEY([CreatedUserID])
 REFERENCES [aspnet_Users]([UserID])
 '')
 END
 END

' ;
print ' [CreatedUserID] Column is created for each user table'

The difficulty is to add the right numbers of  ‘ .

Incoming search terms:

  • sp_msforeachtable

Zeros and strings manipulation in Tsql

How do I convert an int to a zero padded string in T-SQL?

Let’s say I have an int with the value of 1.
How can I convert that int to a zero padded string, such as “00000001″?

DECLARE @iVal int
SET @iVal = 1
select REPLACE(STR(@iVal,8,0),' ','0')

result is “00000001″

And the inverse :

Let’s say you have a string “0000000120345FER” and you want to remove the leading zeros

DECLARE @iVal nvarchar(max)
set @iVal = '0000000120345FER'

DECLARE @iVal2 nvarchar(max)
set @iVal2= SUBSTRING(@iVal, PATINDEX('%[^0]%', @iVal+'.'), LEN(@iVal))

print @iVal2

result is “120345FER”

Copy datatable’s row to another datatable’s row

This method perform a row copy with a few checks


public static void CopyRowToAnother(DataRow row, DataRow anotherRow)
        {

            if (row == null || row.Table == null
                || anotherRow == null || anotherRow.Table == null)
            {
                return;
            }

            foreach (DataColumn colunm in row.Table.Columns)
            {
                if (anotherRow.Table.Columns.Contains(colunm.ColumnName)
                && !row.IsNull(colunm.ColumnName)
                && colunm.DataType == anotherRow.Table.Columns[colunm.ColumnName].DataType)
                {
                    anotherRow[colunm.ColumnName] = row[colunm.ColumnName];
                }
            }
        }

public static void CopyRowToAnOther(DataRow row, DataRow anOtherRow){if (row == null || row.Table == null || anOtherRow == null || anOtherRow.Table==null){

return;

}

foreach (DataColumn colunm in row.Table.Columns)

{

if (anOtherRow.Table.Columns.Contains(colunm.ColumnName)

&& !row.IsNull(colunm.ColumnName)

&& colunm.DataType == anOtherRow.Table.Columns[colunm.ColumnName].DataType)

{

anOtherRow[colunm.ColumnName] = row[colunm.ColumnName];

}

}

}

Incoming search terms:

  • copytodatatable

Don’t repeat your common CSS between your different themes

To fully understand this article, you need an Asp.Net Themes system knowledge

The case :

You have an application with a CSS layout, and a few themes.
There is just a little change between the different themes, by example you change only the color scheme.

A solution to do that is to repeat all CSS files in all your themes, and change the color part.
The problem with that solution is that if you change one common style in one of your themes, you’ll have to repeat the change in all themes. This is a bad idea, it leads to chaos !

Then how can you do to avoid the code repetition ?

My solution:

1.) Create a Common Theme.

In the Common theme, you define CCS files with a default color scheme (just in case)

.testclass
{
border:solid 1px #DDDDDD;
background-color:#DDDDAA;
font-size:xx-large;
}

2.) Create an other Theme (let say “Green”)

In the Green theme, you define CCS files with only the color information


.testclass
{
background-color:#00FF00;
}

2b.) Create an other Theme (let say “Blue”)

In the Green theme, you define CCS files with only the color information


.testclass
{
background-color:#0000FF;
}

Your Theme structure has to look like this :

image

3.) Create a default page (Default.aspx)

And set the class of a div to “testclass” (define above)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ThemTests._Default"
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
 <title>Test with themes</title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Praesent ut tellus eget turpis semper adipiscing sit amet id odio. Nunc vitae imperdiet tellus. Aenean quis orci metus, vitae placerat leo. Pellentesque luctus, lectus sit amet lacinia tincidunt, mi nibh sagittis nisl, vitae mollis eros nisi sit amet sem. Nunc dictum massa turpis. Etiam nisi mauris, consectetur et tempus id, pharetra in turpis. Nullam pretium ultricies nulla nec gravida. Sed porttitor metus nisl, ac condimentum nunc. Integer id feugiat orci. Maecenas ut lacinia purus. Mauris egestas mattis accumsan. Nunc in justo massa, quis egestas orci. Ut tincidunt, ligula sit amet tempor pharetra, magna mauris gravida mi, sed semper magna justo at velit. Donec vulputate fringilla lacus non sollicitudin. Morbi nulla nibh, pellentesque sed bibendum cursus, malesuada ut nisi.
 </div>
 </form>
</body>
</html>

4.) Edit the Web.Config file

Go to the “Pages” section, set the styleSheetTheme attribute to “Common” and the theme attribute to “Green”


<pages styleSheetTheme="Common" theme="Green">

Now launch your web application, the result has to be like this :

image

See in firebug :

image

Explanation :

This works because of the styleSheetTheme and theme attributes.

In fact, ASP.NET applies the styleSheetTheme defined theme first, then applies the page defined styles and at least applies the theme defined theme.

With this technique you can get rid of a few headaches.

Incoming search terms:

  • difference between DTD and CSS in net