x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Code Generataion

3rd and 45? Drop back and Punt? Nah, Generate INSERTS

EDIT 2007/09/06:  I've modified the sproc to change the dates to be formatted to 121 and stripped out all trailing spaces for char's So, we don't have DBArtisan, but I am very happy for my copy of ERWin.  Don't know what I'd do with out it. So we have some requirements where they want to create insert statements to load a production table.  I said, why not just bcp the data out in native format and create an osql script for the production DBA's to insert the data, or a sproc perhaps. There are many better ways in my opinion, but I do...

posted @ Thursday, February 08, 2007 2:39 PM | Feedback (0) | Filed Under [ Code Generataion ]

Generate Triggers for all Tables

Well, I did this originally to generate triggers for all tables in a database to audit data changes, and that is simple enough, just move the entire row from the deleted table to a mirrored audit table. But someone wanted to track activity on tables, so it's a little more simple.  Here we create one log table, and any time a dml operation occurs, it is written there. Enjoy USE Northwind GO CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6)) GO DECLARE @sql varchar(8000), @TABLE_NAME sysname SET NOCOUNT ON SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHILE @TABLE_NAME IS NOT NULL   BEGIN  SELECT @sql = 'CREATE TRIGGER [' +...

posted @ Wednesday, November 29, 2006 9:49 AM | Feedback (0) | Filed Under [ Code Generataion ]

How do I track data changes in a database

This question comes up quit often.  For example in this thread from dbForums SQL Server forum they are looking to do just that. The simplest answer is to create a history for every table, then create a trigger for each that will move the entire row of the before image information into history when a DELETE or UPDATE Modification occurs.  You could type all of this out manually if you'd like.  But I hate typing.  So the code below will generate all of the tables and the triggers for you.  Now I just have to write one of these things for DB2. USE...

posted @ Thursday, August 10, 2006 1:59 PM | Feedback (4) | Filed Under [ SQL Server Code Generataion ]

Generate Fixed width Format Cards (New Version)

Thought I lost this...so I figured I better post it.  It will create format cards for all tables in the database. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[isp_GenFormatCards] GO CREATE PROC isp_GenFormatCards AS DECLARE FormatCard CURSOR FOR SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM ( /* SELECT '--' + TABLE_NAME AS FORMAT_CARD , TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' UNION ALL */ SELECT '7.0' AS FORMAT_CARD , TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' UNION ALL SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD , c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1...

posted @ Thursday, July 06, 2006 2:18 PM | Feedback (2) | Filed Under [ Code Generataion ]

Generate Fixed Width Format Cards

CREATE PROC isp_GenFormatCards AS DECLARE FormatCard CURSOR FOR SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM ( /*  SELECT   '--' + TABLE_NAME AS FORMAT_CARD   , TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping    FROM    INFORMATION_SCHEMA.Tables   WHERE    TABLE_TYPE = 'BASE TABLE'      UNION ALL */  SELECT   '7.0' AS FORMAT_CARD   , TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping    FROM    INFORMATION_SCHEMA.Tables   WHERE    TABLE_TYPE = 'BASE TABLE'      UNION ALL  SELECT    CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD   , c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping    FROM    INFORMATION_SCHEMA.Columns c     INNER JOIN    INFORMATION_SCHEMA.Tables t      ON    c.TABLE_NAME = t.TABLE_NAME     AND    c.TABLE_SCHEMA = t.TABLE_SCHEMA     AND    TABLE_TYPE = 'BASE TABLE'       GROUP BY    c.TABLE_NAME, c.TABLE_SCHEMA      UNION ALL  SELECT    CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)   +...

posted @ Monday, May 15, 2006 11:51 AM | Feedback (1) | Filed Under [ Code Generataion ]

Powered by:
Powered By Subtext Powered By ASP.NET