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'

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'

One Response so far.

1. Aditya Agiwa says:

   March 14, 2011 at 06:41

   ALTER PROCEDURE [dbo].[PHD_SearchPhDStudentForUpdating]

   @Faculty_ID int = null,
   @Seat_No int = null,
   @PHD_Subject_ID bigint = null,
   @Applicant_Name varchar(225) = null,
   @Entrance_Exemption char(1) = null,
   @Bank_ID bigint = null,
   @DD_No varchar(6) = null,
   @Gender char(1) = null,
   @Category_ID bigint = null,
   @Form_No bigint = null,
   @Date_Of_Birth varchar(10) = null,
   @page int = null,
   @pagelength int = null,
   @sortfield varchar(100) = null,
   @IsDescending varchar(10) = null

   AS

   DECLARE @rowcount int
   DECLARE @innerrows int
   DECLARE @sortdesc varchar(100)
   DECLARE @sortasc varchar(100)
   DECLARE @a varchar(6)
   DECLARE @b varchar(6)
   DECLARE @SQL varchar(max)
   DECLARE @CountSql nvarchar(2000)
   DECLARE @Where varchar(200)
   SET @Where = ”
   SET @SQL = ”

   IF @IsDescending=’0′
   BEGIN
   SET @a = ‘ DESC ‘
   SET @b = ‘ ASC ‘
   END
   ELSE
   BEGIN
   SET @a = ‘ ASC ‘
   SET @b = ‘ DESC ‘
   END

   IF charindex(@sortfield, ‘ Form_No’) > 0
   BEGIN
   set @sortdesc = ”
   set @sortasc = ”
   END
   ELSE
   BEGIN
   set @sortdesc = ‘, Form_No ‘ + @a
   set @sortasc = ‘, Form_No ‘ + @b
   END

   IF(@Form_No IS NOT NULL)
   SET @Where = @Where + ‘Form_No = ‘ + cast(@Form_No as varchar) + ‘ AND ‘

   IF(@Seat_No IS NOT NULL)
   SET @Where = @Where + ‘Seat_No = ‘ + cast(@Seat_No as varchar) + ‘ AND ‘

   IF(@Faculty_ID IS NOT NULL)
   SET @Where = @Where + ‘PDE.Faculty_ID= ‘ + cast(@Faculty_ID as varchar) + ‘ AND ‘

   IF(@Applicant_Name IS NOT NULL)
   SET @Where = @Where + ‘Applicant_Name = ”’ + @Applicant_Name + ”’ ‘ + ‘ AND ‘

   IF(@Gender IS NOT NULL)
   SET @Where = @Where + ‘Gender = ”’ + @Gender + ”’ ‘ + ‘ AND ‘

   IF(@Date_Of_Birth IS NOT NULL)
   SET @Where = @Where + ‘Date_Of_Birth = ”’ + @Date_Of_Birth + ”’ ‘ + ‘ AND ‘

   IF(@PHD_Subject_ID IS NOT NULL)
   SET @Where = @Where + ‘PDE.PHD_Subject_ID = ‘ + cast(@PHD_Subject_ID as varchar) + ‘ AND ‘

   IF(@Category_ID IS NOT NULL)
   SET @Where = @Where + ‘Category_ID = ‘ + cast(@Category_ID as varchar) + ‘ AND ‘

   IF(@DD_No IS NOT NULL)
   SET @Where = @Where + ‘DD_No= ”’ + @DD_No + ”’ ‘ + ‘ AND ‘

   IF(@Entrance_Exemption IS NOT NULL)
   SET @Where = @Where + ‘Entrance_Exemption = ”’ + @Entrance_Exemption + ”’ ‘ + ‘ AND ‘

   IF(@Bank_ID IS NOT NULL)
   SET @Where = @Where + ‘Bank_ID = ‘ + cast(@Bank_ID as varchar) + ‘ AND ‘

   SET @CountSql =
   N’ SELECT @rowcount = COUNT(*)
   FROM PhD_Entrance PDE
   INNER JOIN PHD_Subject_Master AS PSM
   ON PSM.PHD_Subject_ID = PDE.PHD_Subject_ID
   AND PSM.Deleted = 0
   WHERE ‘+ @Where + ‘ 1 = 1′

   exec sp_executesql @CountSql, N’@rowcount Int OUTPUT’, @rowcount = @rowcount output

   SET @innerrows = @rowcount – (@page * @pagelength)

   print @innerrows

   SET @SQL =
   ‘SELECT TOP (‘ + str(@pagelength) + ‘) Tmp.*,’+ CONVERT(varchar(9), @rowcount) + ‘As totalRows
   FROM
   (
   SELECT
   TOP (‘ + str(@innerrows) + ‘)
   PDE.Form_No,
   PDE.Applicant_Name,
   IsNull(RIGHT(”0000” + CAST(PDE.Seat_No as varchar),5),”–”)as Seat_No,
   PSM.PHD_Subject_Name,
   COALESCE(cast(Total_Marks as varchar)+ ”/” + cast(Out_Of_Marks as varchar) ,”–”) as Marks
   FROM PhD_Entrance PDE
   INNER JOIN PHD_Subject_Master AS PSM
   ON PSM.PHD_Subject_ID = PDE.PHD_Subject_ID
   AND PSM.Deleted = 0
   WHERE ‘+ @Where + ‘ 1 = 1
   ORDER BY ‘+ @sortfield + @a + @sortdesc + ‘
   )Tmp
   ORDER BY ‘ + @sortfield + @b + @sortasc

   EXEC(@SQL)