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.
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 ONINSERT #tempEXEC sp_msforeachtable 'sp_spaceused ''?'''SELECT a.table_name,b.COLUMN_NAMEFROM #temp aINNER JOIN information_schema.columns bON a.table_name collate database_default= b.table_name collate database_defaultGROUP BY a.table_name, b.COLUMN_NAMEDROP TABLE #tempP.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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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? |
|
|
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 likeselect distinct o.object_id as tblid,OBJECT_NAME(o.object_id) as tblname,c.namefrom sys.objects oinner join sys.columns con c.object_id = o.object_idleft join sys.foreign_keys fon f.parent_object_id = o.object_idwhere o.is_ms_shipped=0and o.type='u'and f.parent_object_id is null ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|