Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

None of the table name falls under more than one schema

Posted on 2013-06-10
4
Medium Priority
?
326 Views
Last Modified: 2013-06-13
In my database there are more than 10 schemas. How to ensure that none of the table  is not fall under more than one schema.

I mean that if a table name say for example tEmployee if falling under the schema [dbo] and [MyDB.Employee] means that should be shown in result.

How to write TSQL to achieve that? Please do assist.
0
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 1064 total points
ID: 39234914
Give this a whirl....

SELECT
TABLE_SCHEMA,
TABLE_NAME,
row_number() over (partition by TABLE_NAME order by Table_Name)
FROM INFORMATION_SCHEMA.TABLES
Order by Table_Name


;With CTE As (
SELECT top 100
TABLE_SCHEMA,
TABLE_NAME,
row_number() over (partition by TABLE_NAME order by Table_Name) As NUMBER
FROM INFORMATION_SCHEMA.TABLES
Order by Table_Name
)
Select * from CTE
where NUMBER > 1
0
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 1064 total points
ID: 39234929
This might work better

;With CTE As (
SELECT top 100
TABLE_SCHEMA,
TABLE_NAME,
row_number() over (partition by TABLE_NAME order by Table_Name) As NUMBER
FROM INFORMATION_SCHEMA.TABLES
Order by Table_Name
)
Select distinct
IST.* 
from CTE C
Inner Join  INFORMATION_SCHEMA.TABLES IST on C.TABLE_NAME = IST.TABLE_NAME
where NUMBER > 1

Open in new window

0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 536 total points
ID: 39235030
I think this one may boil down to personal preference, but here's a different method with joins/grouping:

select
	T1.name as tableName,
	schema_name( T1.schema_id ) as schemaName
from
	sys.tables as T1
		inner join sys.tables as T2 on T1.name = T2.name and T1.schema_id <> T2.schema_id
group by
	T1.name,
	T1.schema_id
order by
	tableName,
	schemaName;	

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39238757
SELECT
    t.name AS table_name,
    SCHEMA_NAME(t.schema_id) AS schema_name
    --,t.*
FROM sys.tables t
WHERE
    --ignore system tables
    t.is_ms_shipped = 0 AND SCHEMA_NAME(t.schema_id) NOT IN (N'INFORMATION_SCHEMA', N'sys') AND
    EXISTS(
        SELECT 1
        FROM sys.tables t2
        WHERE
            --ignore system tables
            t2.is_ms_shipped = 0 AND SCHEMA_NAME(t2.schema_id) NOT IN (N'INFORMATION_SCHEMA', N'sys') AND
            --check table name same but schema name (id) is different
            (t2.name = t1.name AND t2.schema_id <> t1.schema_id)
    )
ORDER BY
    1, 2
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question