/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Wednesday, May 21, 2014

SQL SERVER Add common column to each tables in sql server

JUST WRITE THIS QUERY :


print 'Add  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'''')
 AND name = (''''YOUR_TABLE_NAME_HERE'''')

 )
 BEGIN

 IF columnproperty(object_id(''''?''''), ''''CreatedBy'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedBy] [nvarchar](100) NULL
 END

 IF columnproperty(object_id(''''?''''), ''''CreatedOn'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedOn] [datetime]  NULL
 END

 IF columnproperty(object_id(''''?''''), ''''UpdatedBy'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [UpdatedBy] [nvarchar](100) NULL
 END

 IF columnproperty(object_id(''''?''''), ''''UpdatedOn'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [UpdatedOn] [datetime]  NULL
 END

 IF columnproperty(object_id(''''?''''), ''''deletedby'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [deletedby] [nvarchar](100) NULL
 END

 IF columnproperty(object_id(''''?''''), ''''deletedon'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [deletedon] [datetime]  NULL
 END

 END
 END
'')

' ;

print ' Column is created for each user table'

ONE ANOTHER QUERY:

print 'Add  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(''''?''''), ''''CreatedBy'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedBy] VARCHAR (200) NULL
 END

  IF columnproperty(object_id(''''?''''), ''''CreatedOn'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedOn] datetime NOT NULL
 END


 END
 END
'')

' ;
print ' Column is created for each user table'



No comments:

Post a Comment

Recent Posts

My Blog List