1: -- ==========================================================================

   2: -- Object       : SQLCMD-SSMS.sql

   3: -- Object Type  : Script

   4: -- Description  : Various examples

   5: -- Developer    : Donabel Santos

   6: -- Origin       : 2008/08/01

   7: -- Last Modified: 2008/08/05

   8: -- Notes        : 

   9: --               This needs to be run in SSMS in SQLCMD mode

  10: -- ==========================================================================

  11:

  12: -- SHORTCUTS

  13: -- F1 Help

  14: -- Ctrl+ Shift+M replace template parameters

  15:

  16: -- --------------------------------------------------------------------------

  17: -- How to use an output file to store query output

  18: -- --------------------------------------------------------------------------

  19: :out C:Tempsampleoutput.txt

  20:

  21: SET NOCOUNT ON

  22:

  23: SELECT

  24:     @@VERSION AS 'Server Version',

  25:     GETDATE() AS 'Current Date'

  26:

  27: SET NOCOUNT OFF

  28:

  29:

  30: -- --------------------------------------------------------------------------

  31: -- How to set a variable

  32: -- Note variables must be run in the same batch

  33: -- --------------------------------------------------------------------------

  34:

  35: -- sample 1

  36: :setvar dbname AdventureWorks

  37: :setvar col1 DepartmentID

  38: :setvar col2 Name

  39: :setvar tablename HumanResources.Department

  40:

  41: SELECT '$(dbname)' AS dbname

  42:

  43: -- use the database

  44: USE $(dbname)

  45: SELECT

  46:     DB_NAME() AS 'DB_NAME()',

  47:     DB_ID()   AS 'DB_ID()'

  48:

  49: -- select from the table

  50: SELECT

  51:     $(col1),

  52:     $(col2)

  53: FROM

  54:    $(tablename)

  55:

  56:

  57: -- sample 2

  58: -- here we are setting several environment variables

  59: :setvar workpath C:Temp

  60: :setvar outfile out.txt

  61: :setvar errfile err.txt

  62: :out      $(workpath)$(outfile)

  63: :Error    $(workpath)$(errfile)

  64:

  65: -- output of this will go to file specified in :out

  66: SELECT @@VERSIONS

  67:

  68:

  69: -- --------------------------------------------------------------------------

  70: -- How to execute a script file

  71: -- --------------------------------------------------------------------------

  72:

  73: -- sample 1

  74: -- connect to a different database

  75: -- make sure you are in SQLCMD mode

  76: USE test

  77: :r Y:DropTable.sql

  78: GO

  79: :r Y:CreateTable.sql

  80: GO

  81:

  82: SELECT

  83:     DB_NAME()    AS 'Database',

  84:     [name]        AS 'Table',

  85:     create_date    AS 'Created',

  86:     DATEDIFF(second,create_date, GETDATE()) AS 'Seconds Ago'

  87: FROM

  88:     sys.objects

  89: WHERE

  90:     type = 'U'

  91: ORDER BY

  92:     create_date DESC

  93:

  94: -- sample2

  95: :setvar dbname Test

  96: :setvar workpath Y:

  97: :setvar file1 DropTable.sql

  98: :setvar file2 CreateTable.sql

  99: :setvar errfile err.txt

 100:

 101: USE $(dbname)

 102: :r $(workpath)$(file1)

 103: :r $(workpath)$(file2)

 104:

 105: SELECT

 106:     DB_NAME()    AS 'Database',

 107:     [name]       AS 'Table',

 108:     create_date   AS 'Created',

 109:     DATEDIFF(second,create_date, GETDATE()) AS 'Seconds Ago'

 110: FROM

 111:     sys.objects

 112: WHERE

 113:     type = 'U'

 114: ORDER BY

 115:     create_date DESC