Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to get list of master tables in database

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-28 : 08:35:03
how to get list of master tables in database along with columns
here i found out one query which is giving entire tables in database but i need to get the list of master tables along with columns

suggest me :

CREATE TABLE #temp (
table_name sysname ,
COLUMN_NAME VARCHAR(150),
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
b.COLUMN_NAME

FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, b.COLUMN_NAME

DROP TABLE #temp

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 08:37:14
what do you mean by list of master databases? do you've multiple instances on the same box?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-28 : 08:48:49
No visakh...in a database there will be transaction tables and master tables while i am executing getting all the entire tables in my database.i need to get only master tables not any child tables.

P.V.P.MOhan
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 08:57:26
The terms "master table" and "transaction table" are not terms that are generally used or generally known in SQL Server community for categorizing tables in a database. It may be that they are specific to the business logic/schema of your database. Is there some characteristic that identifies a master table as being so?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 08:57:37
do you mean parent tables? if yes use logic like


select distinct o.object_id as tblid,OBJECT_NAME(o.object_id) as tblname,c.name
from sys.objects o
inner join sys.columns c
on c.object_id = o.object_id
left join sys.foreign_keys f
on f.parent_object_id = o.object_id
where o.is_ms_shipped=0
and o.type='u'
and f.parent_object_id is null


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 09:04:51
other wise there should be a specific naming convention followed for you to identify your master tables easily. There's no generic way for system to know which one of those objects are master entry based for you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -