در زمان پیاده سازی دیتابیس یک پروژه؛ چیزی که توجهم را به خود جلب کرد این بود که چهار فیلد ( InsertedBy, InsertDateTime, ModifiedBy, ModifyDateTime ) در تمامی جداول (98%) تکرار شده بود, طبیعتا ایجاد 232 فیلد (4 فیلد تکراری در 58 جدول) تکراری کار خسته کننده ای هست.
خب چرا از طریق یک داینامیک کوئری این کار را به صورت اتوماتیک انجام ندهیم؟
به این صورت که با استفاده از یک کرزر (Cursor) لیست جداول دیتابیس پیمایش می شوند, سپس به ازای هر یک از جداول, با استفاده از دو کوئری ابتدا وجود فیلد مورد نظر بررسی می شود, و در صورت عدم وجود آن؛ کوئری اضافه شدن فیلد اجرا می شود.
به کد زیر دقت کنید:
USE My_DB
GO
DECLARE @TableName VARCHAR(30)
DECLARE @Query VARCHAR(MAX)
DECLARE AlterTable_Cursor CURSOR
FOR
SELECT SCHEMA_NAME(schema_id) + '.' + t.name
FROM sys.tables AS t
OPEN AlterTable_Cursor
FETCH NEXT FROM AlterTable_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = 'IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ''dbo.''+TABLE_NAME = ''' + @TableName + '''
AND COLUMN_NAME = ''InsertedBy'')
ALTER TABLE ' + @TableName + ' ADD InsertedBy int not NULL
IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ''dbo.''+TABLE_NAME = ''' + @TableName + '''
AND COLUMN_NAME = ''InsertDateTime'')
ALTER TABLE ' + @TableName + ' ADD InsertDateTime datetime not NULL
IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ''dbo.''+TABLE_NAME = ''' + @TableName + '''
AND COLUMN_NAME = ''ModifiedBy'')
ALTER TABLE ' + @TableName + ' ADD ModifiedBy int NULL
IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ''dbo.''+TABLE_NAME = ''' + @TableName + '''
AND COLUMN_NAME = ''ModifyDateTime'')
ALTER TABLE ' + @TableName + ' ADD ModifyDateTime datetime NULL
'
--PRINT @Query
EXEC(@Query)
FETCH NEXT FROM AlterTable_Cursor INTO @TableName
END
CLOSE AlterTable_Cursor
DEALLOCATE AlterTable_Cursor
توجه: می توان در صورت نیاز ابتدا ستون موجود را حذف و مجددا اقدام به ایجاد آن نمود؛
همچنین دقت داشته باشید در صورتی که جداول شما دارای رکورد باشند؛ برای ایجاد ستون هایی که Not Null می باشند؛ پیغام خطا صادر خواهد شد.
با کمی دقت و حوصله می توان این کوئری را با صورت دلخواه درآورد و از انجام مشقت بار کارهای تکراری از این دست جلوگیری نمود.
