0

I am trying to add a column to a table whereby - we check to see if the table exists in each database and - checks to see if the column exists and only adds if it doesn't

I've tried a number of things but getting stuck.

DECLARE @sql nvarchar(MAX);

SELECT
    @sql = ISNULL(@sql, N'') + 'if col_length('+ QUOTENAME(d.name) + '''apartment_type'',''type_category'''+') is null begin'+
        N'ALTER TABLE ' + QUOTENAME(d.name) + N'.[dbo].[apartment_type] ADD type_category varchar(100);'
    FROM sys.databases d
    WHERE d.name in ('1001_CA','1003_GW','1004_TP','1010_SR','1013_SS','1026_SA','1027_TE','1028_RP','1030_VB','1031_RA','1033_PO','1034_FO','1036_WL','1037_RI','1038_PM','1040_HC','1041_WS','1042_RT','1043_PC','1044_LC','1045_FR','1046_PW','1047_LB','1048_BH','1049_NL','1050_SW','1051_SC','1052_PC','1053_WE','1054_CS','1055_BH','1056_MW','1057_BN','1058_CW','1059_BP','1060_WM','1061_WU','1062_OD','1063_CO','1064_SI','1065_SH','1066_SL','1067_AP','1068_VL','1069_AN','1070_TM','1071_FV','1072_CG','1073_FS','1074_HM','1075_ES','1076_CP','1077_WT','1078_CP','1079_CR','1080_CA','1081_TU','1082_VW','1083_CM','1084_DM','1085_LM','1086_MD','1087_BS','1088_ST','1089_BW','1090_MO','1091_CZ','1092_DO','1093_LD','1094_MA','1095_BR','1096_AX','1097_P9','1098_VI','1099_SO','1100_FA','1101_ZA','1102_BL','1103_VN','1104_VE','1105_CL','1106_SP','1107_BV','1108_PX','1109_MI','1110_GN','1111_WP','1112_PA','1113_GH','1114_CC','1114_IL','1115_IP','1116_CC');
        /* TODO: filter me more? */

BEGIN TRANSACTION;
    EXEC(@sql);
COMMIT TRANSACTION;
user2772056
  • 135
  • 1
  • 2
  • 9

2 Answers2

2
Refer Below site for check all databases :

Search for a string in all databases, all columns, and all tables (SQL Server 2008 R2)

For particular database use below script :

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'columnName'
   AND Object_ID = Object_ID(N'tableName'))
BEGIN
    -- Create column script here
END
Community
  • 1
  • 1
Mansoor
  • 4,061
  • 1
  • 17
  • 27
0

you can use sp_msforeachdb advanced version..

EXEC sp_MSforeachdb N'
IF db_id()>4 
begin 
if not exists(select 1 from sys.columns where object_id=object_id(''tablename'')) and name=''somecol'')
Begin
alter table tablename add column datattype
end
end'
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94