SQLCMD Examples

Here are some samples on how to use SQLCMD in SSMS.

   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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s