Advanced Search

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.

Expand | Embed | Plain Text

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.