Create an extra table (copy the columns and new table) for every existing table

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 426,256 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,087 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1250 | Replies: 1

Previous Post in Thread

Next Post in Thread

•

Join Date: Mar 2008

Posts: 1

Reputation: sauronbrad is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

sauronbrad sauronbrad is offline

Offline

Newbie Poster

Create an extra table (copy the columns and new table) for every existing table

Mar 28th, 2008

Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.

Here is my code:

Help with Code Tags

(Toggle Plain Text)

USE pubs

DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'

WHILE @TABLE_NAME IS NOT NULL
BEGIN

    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_NAME = 'sales'

    SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''


    SELECT * INTO @AUDIT_TABLE
    FROM @TABLE_NAME

    TRUNCATE TABLE @AUDIT_TABLE
    ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime  Char(50),AuditUser Char(50)


    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_TYPE= 'BASE TABLE'
    AND TABLE_NAME!= 'sysdiagrams'
    AND TABLE_NAME!= 'Audit'
    AND TABLE_NAME NOT LIKE 'audit%'

END

Thanks. ..