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
• pictured9pn
• shoottjy

Tweet

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”

Tweet