February 22, 2010 at 1:29 pm
Hi All,
In a SQL 2005 database, is there any way to find master and transaction tables? I don't have the database diagram. I need to find which tables is master table and which one is transactional tables. Please help me out.
Regards
Moosa
February 22, 2010 at 1:38 pm
Not really sure what you're asking here. Are you wanting to know parent to child relationships?
Without a diagram, you can't really tell at a glance, but you could trace out based on existing foreign keys, assuming those are in place.
February 22, 2010 at 2:44 pm
Sorry for the confusion. In a datbase, i need to find what are all the master tables and what are the transactional tables.
February 23, 2010 at 9:09 am
I don't understand what you mean by 'master' and 'transactional' tables.
There is a master database. Is this what you mean? A transactional table is any table that stores transaction information. Every application will be different.
Somehow, I don't think that's what you mean.
February 23, 2010 at 10:35 am
I'm wondering if he means lookup tables and transactional data tables. Examples: lookup tables such as Customer or Customer Address and transactional tables such as Sales.
If this IS what is meant then a diagram is a great way to see it. Also a top level transactional table usually have lots of foreign keys so that is often a hint.
CEWII
February 24, 2010 at 8:39 am
By master tables do you mean the system tables?
Or maybe what you're looking for are lookup tables, tables that contain relatively static data for use as referential integrity to provide consistent data for tables that are constantly changing?
Basically, the terminology you're using, isn't universal, so you need to help us out and explain what you mean.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2010 at 6:06 am
moosamca (2/22/2010)
In a datbase, i need to find what are all the master tables and what are the transactional tables.
What i got from other posts and your query is, you treat master table which contains master information like "Employee_Master" while "Employee_salarydetails" will be treated as transactional table.
But whatever the case , we cant really figure out which are transactional and masters.
but below query might help you. it will give you probable transactional table
DECLARE @Tmp table ( object_id nvarchar(200), page_count int )
INSERT INTO @Tmp
select object_id as object_id , page_count FROM sys.dm_db_index_physical_stats (db_id('test_db'), NULL, NULL, NULL, NULL)
WHERE page_count > 1
SELECT DISTINCT s.NAME FROM sys.sysobjects s
INNER JOIN @Tmp t
ON t.object_id = s.id
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply