August 4, 2004 at 6:23 am
I want to create a Data Dictionary in SQL Server 2000(concept taken from Oracle, but with a little variation) and in that I want to capture all the table names, DB name , last updated on , last updated (by) Login, Rows before update , Rows after update.
This I want to build for one server, and later I want to expand this to all the SQL Servers.
How can I do this (without creating triggers on all the tables) in SQL ?
August 5, 2004 at 6:40 am
As far as I know SQL server does not track table updates so you would need to use triggers to do that.
You can get table name info from sysobjects where xtype='U' and columns from syscolumns.
try sp_help
You can do:
>sp_helptext sp_help
to learn how this is done.
The following procedure will give you table size and number of rows. Its a bit more complex than you need as I use dynamic SQL to run it from a non-master database:
CREATE PROCEDURE usp_SpaceUsed
(
@DatabaseName sysname = NULL,
@NumRows int=9999-- Nubmer of rows to return
)
AS
/********************************************************************************
This is a tailored version of sp_spaceused
Lists space used by all tables in descending order of size
Plus one summary row at start showing space used by all tables
*********************************************************************************/
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Declare holding variable for dynamic query
DECLARE @cmd varchar(1000)
DECLARE @DBNamesysname
SELECT @DBName = ISNULL(@DatabaseName, DB_NAME())-- This is not much use now as current is always DataDict
-- Verify that the supplied database exists
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases
WHERE Name = @DBName)
BEGIN
RAISERROR(15010,1,1,@DBname) WITH SETERROR
RETURN 15010
END
SELECT @cmd='USE ' + @DBName + char(13) + char(10)
-- Build it (and they will come...)
SELECT @cmd = @cmd +
'
SELECT TOP ' + convert(varchar,@NumRows) +'
''Table'' =object_name(S.id),
''Reserved KB'' = convert(bigint, sum(CASE WHEN S.indid IN (0,1,255) THEN S.reserved ELSE 0.0 END) * min(D.low) / 1024),
''Data KB'' = convert(bigint,sum(CASE WHEN S.indid IN (0,1) THEN dpages WHEN S.indid=255 THEN isnull(S.used,0.0) ELSE 0.0 END) * min(D.low) / 1024),
''Index KB'' = convert(bigint,sum(CASE WHEN S.indid IN (0,1) THEN isnull(S.used,0.0) - S.dpages ELSE 0.0 END) * min(D.low) / 1024),
''Unused KB'' = convert(bigint,sum(CASE WHEN S.indid IN (0,1,255) THEN S.reserved - S.used ELSE 0.0 END) * min(D.low) / 1024),
''Rows'' = sum(CASE WHEN S.indid IN (0,1) THEN S.rowcnt ELSE 0 END)
FROM sysindexes S, master.dbo.spt_values D
WHERE D.number = 1 AND D.type = ''E'' AND S.Reserved > 0
GROUP BY object_name(S.id) WITH ROLLUP
ORDER BY 2 desc'
-- We've built it, time to execute
EXEC (@cmd)
-- Hopefully, this is zero
RETURN @@ERROR
GO
August 5, 2004 at 8:53 am
Thanks. So I have to create triggers on all tables if I want to capture when and how it was last updated and what login was used to update the table. Is there a way to create triggers on all the tables of a DB.?
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply