SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to get list of master tables in database		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Posting Yak Master

India
208 Posts
Posted - 01/28/2013 :  08:35:03  Show Profile  Reply with Quote
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

India
48012 Posts
Posted - 01/28/2013 :  08:37:14  Show Profile  Reply with Quote
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
Posting Yak Master

India
208 Posts
Posted - 01/28/2013 :  08:48:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1730 Posts
Posted - 01/28/2013 :  08:57:26  Show Profile  Reply with Quote
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

India
48012 Posts
Posted - 01/28/2013 :  08:57:37  Show Profile  Reply with Quote
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

India
48012 Posts
Posted - 01/28/2013 :  09:04:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000