Powershell & SQL - Add unique key identity column


/ Published in: Windows PowerShell
Save to your folder(s)

Adds unique key identity column to tables based off of csv. Custom script for Kronos.


Copy this code and paste it in your HTML
  1. Import-Module SQLServer
  2.  
  3. # Declare & set primary variables/constants
  4. $SQLServer = "KRONOSDBARC"
  5. $SQLDB = "TKCSDB"
  6. $NewIndexCol = "UQKey"
  7.  
  8. $dataSource=import-csv “q:\temp\tables.csv”
  9.  
  10. foreach($dataRecord in $datasource)
  11. {
  12. $NewIndex = $NewIndexCol + "_" + $dataRecord.Table
  13.  
  14. $SQLQuery = "Delete from " + $dataRecord.Table
  15. write $SQLQuery
  16. Set-SqlData -sqlserver $SQLServer -dbname $SQLDB -qry $SQLQuery
  17.  
  18. $SQLQuery = "Alter Table " + $dataRecord.Table + " add " + $NewIndexCol + " int identity not null"
  19. write $SQLQuery
  20. Set-SqlData -sqlserver $SQLServer -dbname $SQLDB -qry $SQLQuery
  21.  
  22. $SQLQuery = "Create unique index " + $NewIndex + " on " + $dataRecord.Table + "(" + $NewIndexCol + ")"
  23. write $SQLQuery
  24. Set-SqlData -sqlserver $SQLServer -dbname $SQLDB -qry $SQLQuery
  25. }
  26.  
  27. <# q:\temp\tables.csv
  28. Table,keyCols,type,Row Count
  29. ARCHIVEEMAILS,<none>,,0
  30. ARCMETADATASTR,<none>,,0
  31. ARCPROCESSMAP,<none>,,0
  32. ASSIGNMENTRULE,**Needs key with non-nullable columns**,,0
  33. COLUMNCONFIGMM,<none>,,240
  34. COMBINATION,<none>,,1
  35. COMBINATNKEY,<none>,,12
  36. ES_TEMP,<none>,,0
  37. FCOPERHRSITEM,<none>,,0
  38. FCTIMESCALEITM,<none>,,0
  39. KCSDBINITDATA,<none>,,0
  40. KCSONEROW,<none>,,1
  41. KCSUPGRADEHISTORY,<none>,,78
  42. LLSETLLEIMPORT,<none>,,0
  43. MINORRULEDAY,<none>,,0
  44. MINORRULEWEEK,<none>,,0
  45. MYWTKEMPLOYEE,**Needs key with non-nullable columns**,,31634
  46. MYWTKGENIEEMPS,**Needs key with non-nullable columns**,,0
  47. MYWTKSCHEDEMPS,**Needs key with non-nullable columns**,,0
  48. NOTIFMSGQUEUE,**Needs key with non-nullable columns**,,0
  49. PERSONDATEIMPORT,<none>,,0
  50. PERSONMANYIMPORT,<none>,,0
  51. PRGDPRSNDEVUPD,<none>,,757
  52. PT_TEMP,<none>,,0
  53. PUNCHEVENTTRC,<none>,,907287
  54. RECENTENTRY,<none>,,941
  55. RPTHFREPLACE,<none>,,0
  56. SCHEDPATRNDATE,<none>,,1
  57. SEQCHECK,<none>,,0
  58. SEQUENCEDITEM,**Needs key with non-nullable columns**,,7
  59. SHFTASGNMNTTRC,<none>,,3123600
  60. TI_TEMP,<none>,,0
  61. TIMESHTITMTRC,<none>,,54103585
  62. TP_TEMP,<none>,,0
  63. WFC_TEMPCOLUMNS,<none>,,0
  64. WFC_TEMPOBJECTS,<none>,,0
  65. WFCAUDIT,<none>,,2310481
  66. WFCGLOBALS,<none>,,1
  67. WFCSEGMENTMAP,<none>,,9
  68. WMTDEVICEUPDATE,**Needs key with non-nullable columns**,,0
  69. WPMSGRPSCHBDNOTS,**Needs key with non-nullable columns**,,0
  70. WPMSHFTDAYBDNOTS,**Needs key with non-nullable columns**,,0
  71. WPMSSCPTRNBDNOTS,**Needs key with non-nullable columns**,,0
  72. WPMVACBIDNOTES,**Needs key with non-nullable columns**,,0
  73. WRMEMAILADDRES,<none>,,0
  74. WRMJOBSTATHIST,<none>,,0
  75. WRMMDCOLUMNVAL,<none>,,0
  76. WRMMDROWSELDTA,<none>,,0
  77. WRMRETROCPHIST,<none>,,0
  78. WRMRTRCPENDVER,<none>,,0
  79. WRMTASKPROCMAP,<none>,,0
  80. WTDOPTOUTSTAT,<none>,,0
  81. #>

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.