Posted By

HarleyAR on 07/13/12


Tagged


Versions (?)

Powershell & SQL - Add unique key identity column


 / Published in: Windows PowerShell
 

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

  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  

You need to login to post a comment.