belle’s sql musings

Generating Random Data for SQL Server and MySQL

Posted in DBA Toolbox / T-SQL Scripts, MySQL, T-SQL Tips and Tricks by belle on October 5, 2008

SQL Server – uses NEWID() in ORDER BY

   1: -- using the AdventureWorks Database
   2: SELECT
   3:     TOP 10
   4:     DepartmentID,
   5:     [Name]
   6: FROM
   7:     HumanResources.Department
   8: ORDER BY
   9:     NEWID()
  10:

MySQL – uses RAND() in ORDER BY

   1: SELECT
   2:   `Code`,
   3:   `Name`
   4: FROM
   5:   `Country`
   6: ORDER BY
   7:   RAND()
   8: LIMIT 10;
Tagged with:

1 comment

Using DDL Triggers

Posted in DBA Toolbox / T-SQL Scripts, Issues/Troubleshooting, T-SQL Tips and Tricks by belle on October 5, 2008

Sample script below:

   1: -- ==========================================================================
   2: -- Object       : DDLTrigger.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Notes        : 
   6: -- ==========================================================================
   7:
   8: -- DDL Triggers
   9: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d79e5725-adea-4934-9474-9cd975adb6d8.htm
  10:
  11: -- DDL Trigger Events
  12: -- http://msdn.microsoft.com/en-us/library/ms189871.aspx
  13:
  14: -- EVENTDATA
  15: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/03a80e63-6f37-4b49-bf13-dc35cfe46c44.htm
  16: -- EVENTDATA function returns XML data which contains the information about server 
  17: -- or database events. 
  18:
  19: -- sample EVENTDATA()
  20: /*
  21: <EVENT_INSTANCE>
  22:   <EventType>CREATE_TABLE</EventType>
  23:   <PostTime>2008-10-05T01:23:12.030</PostTime>
  24:   <SPID>55</SPID>
  25:   <ServerName>JUBILEE\SQL01</ServerName>
  26:   <LoginName>JUBILEE\Administrator</LoginName>
  27:   <UserName>dbo</UserName>
  28:   <DatabaseName>AdventureWorks</DatabaseName>
  29:   <SchemaName>dbo</SchemaName>
  30:   <ObjectName>t</ObjectName>
  31:   <ObjectType>TABLE</ObjectType>
  32:   <TSQLCommand>
  33:     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
  34:                 QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
  35:     <CommandText>CREATE TABLE t
  36:         (
  37:             id int
  38:         )
  39: </CommandText>
  40:   </TSQLCommand>
  41: </EVENT_INSTANCE>
  42:
  43:  */
  44:
  45: -- ---------------------------------------------------------------------
  46: -- database scope
  47: -- SSMS: Database > Programmability > Database Triggers
  48: -- ---------------------------------------------------------------------
  49: USE AdventureWorks
  50: GO
  51:
  52: IF EXISTS(
  53:   SELECT *
  54:     FROM sys.triggers
  55:    WHERE name = N'tr_db_ddl_NoCreateTable'
  56:      AND parent_class_desc = N'DATABASE'
  57: )
  58: BEGIN
  59:     DROP TRIGGER tr_db_ddl_NoCreateTable ON DATABASE
  60: END
  61: GO
  62:
  63: CREATE TRIGGER tr_db_ddl_NoCreateTable
  64: ON DATABASE -- current database
  65: FOR CREATE_TABLE, DROP_TABLE
  66: AS
  67: BEGIN
  68:     SELECT
  69:         'tr_db_ddl_NoCreateTable' 'Trigger',
  70:         'You are not permitted to perform selected action' 'Msg2',
  71:         EVENTDATA() 'EVENTDATA()'
  72:     ROLLBACK
  73: END
  74: GO
  75:
  76: -- test
  77: CREATE TABLE t
  78: (
  79:     id int
  80: )
  81: GO
  82:
  83:
  84: -- ---------------------------------------------------------------------
  85: -- server scope
  86: -- SSMS: Server Objects > Database Triggers
  87: -- ---------------------------------------------------------------------
  88: USE AdventureWorks
  89: GO
  90:
  91: IF EXISTS(
  92:    SELECT *
  93:    FROM sys.server_triggers
  94:    WHERE name = N'tr_srv_ddl_DisplayServerEvents'
  95: )
  96: BEGIN
  97:     DROP TRIGGER tr_srv_ddl_DisplayServerEvents ON ALL SERVER
  98: END
  99: GO
 100:
 101:
 102: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
 103: ON ALL SERVER
 104: FOR CREATE_DATABASE, CREATE_LOGIN
 105: AS
 106: BEGIN
 107:     SELECT
 108:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
 109:         'You are not permitted to perform selected action' 'Msg',
 110:         EVENTDATA() 'EVENTDATA()'
 111:     ROLLBACK
 112: END
 113: GO
 114:
 115: -- test
 116: CREATE DATABASE db
 117: GO

It is also interesting to note that creating a server-scoped trigger for DDL_SERVER_SECURITY_EVENTS

Msg 1082, Level 15, State 1, Procedure tr_srv_ddl_DisplayServerEvents, Line 11
“DROP_SERVER_ROLE_MEMBER” does not support synchronous trigger registration.

The trigger that fails follows:

   1: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
   2: ON ALL SERVER
   3: FOR DDL_SERVER_SECURITY_EVENTS
   4: AS
   5: BEGIN
   6:     SELECT
   7:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
   8:         'You are not permitted to perform selected action' 'Msg',
   9:         EVENTDATA() 'EVENTDATA()'
  10:     ROLLBACK
  11: END
  12: GO
  13:

Not sure why. Seems the DDL_SERVER_SECURITY_EVENTS should include only server-scoped events.

DDL Trigger Events

Will continue to dig in; when I find additional details I will post a follow up entry to this one.

Tagged with: ,

1 comment

Using the OUTPUT clause in SQL Server 2005

Posted in DBA Toolbox / T-SQL Scripts, T-SQL Tips and Tricks by belle on October 5, 2008 
   1: -- ==========================================================================
   2: -- Object       : Output.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Origin       : 2008/06/17
   6: -- Last Modified: 2008/10/04
   7: -- Notes        : 
   8: -- ==========================================================================
   9:
  10: -- The OUTPUT clause allows you to display or manipulate rows affected by 
  11: -- INSERT/UPDATE/DELETE
  12: -- Similar to the inserted and deleted tables in a trigger
  13:
  14:
  15: USE AdventureWorks
  16: GO
  17:
  18: IF OBJECT_ID('dbo.DepartmentCopy') IS NOT NULL
  19:   DROP TABLE dbo.DepartmentCopy
  20: GO
  21:
  22: SELECT
  23:     *
  24:     INTO dbo.DepartmentCopy
  25: FROM
  26:     HumanResources.Department
  27:

  28:
  29: -- ---------------------------------------------------------------------
  30: -- display deleted values
  31: -- ---------------------------------------------------------------------
  32: DELETE dbo.DepartmentCopy
  33:     ---------------------------------------------
  34:     OUTPUT 
  35:         deleted.DepartmentID        AS 'Deleted ID',
  36:         deleted.[Name]              AS 'Deleted Name'
  37:     ---------------------------------------------
  38: WHERE 
  39:     DepartmentID > 10
  40:
  41:
  42:
  43: -- ---------------------------------------------------------------------
  44: -- display inserted values PLUS capture inserted values 
  45: -- ---------------------------------------------------------------------
  46: DECLARE @InsertedDepartment TABLE
  47: (
  48:    DepartmentID    smallint,
  49:     [Name]         varchar(50)
  50: )
  51:
  52: INSERT INTO dbo.DepartmentCopy([Name], GroupName, ModifiedDate)
  53:     ---------------------------------------------
  54:     OUTPUT
  55:         inserted.DepartmentID        AS 'Inserted ID',
  56:         inserted.[Name]              AS 'Inserted Name'
  57:     INTO @InsertedDepartment (DepartmentID, [Name])
  58:     ---------------------------------------------
  59: SELECT
  60:     [Name],
  61:     GroupName,
  62:     GETDATE()
  63: FROM
  64:     HumanResources.Department
  65: WHERE
  66:     DepartmentID > 10
  67:
  68: -- display what is captured
  69: SELECT
  70:     *
  71: FROM
  72:     @InsertedDepartment
Tagged with: ,

leave a comment

Script to Drop/Recreate CHECK Constraints

Posted in DBA Toolbox / T-SQL Scripts by belle on October 4, 2008 
   1: -- ===========================================================
   2: -- Check Constraints
   3: -- How to script out Check Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- Drop Check Constraints
   8: SELECT
   9:     'ALTER TABLE  ' +
  10:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(CONSTRAINT_NAME)
  14: FROM
  15:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  16:     INNER JOIN sys.sysobjects so
  17:     ON cc.CONSTRAINT_NAME = so.[name]
  18:
  19: -- Recreate Check Constraints
  20: SELECT
  21:     'ALTER TABLE  ' +
  22:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  23:     CHAR(10) +
  24:     ' ADD CONSTRAINT ' +
  25:     QuoteName(CONSTRAINT_NAME) +
  26:     ' CHECK ' +
  27:     CHECK_CLAUSE
  28: FROM
  29:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  30:     INNER JOIN sys.sysobjects so
  31:     ON cc.CONSTRAINT_NAME = so.[name]

Here is a sample result:

ALTER TABLE  [ProductReview]
 DROP CONSTRAINT [CK_ProductReview_Rating]
ALTER TABLE  [ProductReview]
 ADD CONSTRAINT [CK_ProductReview_Rating] CHECK ([Rating]>=(1) AND [Rating]<=(5))
Tagged with: , ,

leave a comment

SQLCMD Examples

Posted in DBA Toolbox / T-SQL Scripts by belle on August 6, 2008

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:\Temp\sampleoutput.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 :o ut
  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
Tagged with: ,

leave a comment