T-SQL Remove Duplicate Columns on this Query

Question

I have a view "TableInformationView".

USE [AdventureWorks]
--OR ANY DATABASE
/****** Object:  View [dbo].[TableInformationView]    Script Date: 01/01/2011 01:17:31 ******/
IF EXISTS (SELECT *
           FROM   sys.views
           WHERE  object_id = OBJECT_ID(N'[dbo].[TableInformationView]'))
  DROP VIEW [dbo].[TableInformationView]

GO

/****** Object:  View [dbo].[TableInformationView]    Script Date: 01/01/2011 01:17:31 ******/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[TableInformationView]
AS
  SELECT C.TABLE_SCHEMA                              AS SchemaName,
         C.TABLE_NAME                                AS BaseTableName,
         CASE
           WHEN C.TABLE_SCHEMA = 'dbo' THEN C.TABLE_NAME
           ELSE C.TABLE_SCHEMA + '.' + C.TABLE_NAME
         END                                         AS TableName,
         C.COLUMN_NAME                               AS ColumnName,
         C.ORDINAL_POSITION                          as OrdinalPosition,
         C.COLUMN_DEFAULT                            as ColumnDefault,
         C.IS_NULLABLE                               AS IsNullable,
         C.DATA_TYPE                                 as DataType,
         ISNULL(C.CHARACTER_MAXIMUM_LENGTH, 0)       as MaxLength,
         TC.CONSTRAINT_TYPE                          AS ConstraintType,
         TC.CONSTRAINT_NAME                          as ConstraintName,
         RC.UNIQUE_CONSTRAINT_NAME                   as ParentConstraintName,
         RC.MATCH_OPTION                             as MatchOption,
         RC.UPDATE_RULE                              as UpdateRule,
         RC.DELETE_RULE                              as DeleteRule,
         TC_FK.TABLE_SCHEMA + '.' + TC_FK.TABLE_NAME 'ForeignTable'
  FROM   INFORMATION_SCHEMA.COLUMNS C
         LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
           ON C.TABLE_NAME = KCU.TABLE_NAME
              AND C.COLUMN_NAME = KCU.COLUMN_NAME
              AND C.TABLE_SCHEMA = KCU.TABLE_SCHEMA
              AND C.ORDINAL_POSITION = KCU.ORDINAL_POSITION
         LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
           ON KCU.TABLE_NAME = TC.TABLE_NAME
              AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
              AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
              AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
              AND KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
         LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
           ON RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
              AND RC.UNIQUE_CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
         LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK
           ON RC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
              AND RC.UNIQUE_CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC_FK
           ON RC.UNIQUE_CONSTRAINT_NAME = TC_FK.CONSTRAINT_NAME
              AND RC.UNIQUE_CONSTRAINT_SCHEMA = TC_FK.CONSTRAINT_SCHEMA

GO

The view works fine except for the columns which are foreign keys of another table.

See How

SELECT * FROM TableInformationView 
WHERE 
    TableInformationView.SchemaName + 
    TableInformationView.BaseTableName + 
    TableInformationView.ColumnName

    IN
    (
    select TOP 1
        TableInformationView.SchemaName + 
        TableInformationView.BaseTableName + 
        TableInformationView.ColumnName
    from 
    TableInformationView 
    group by
        TableInformationView.SchemaName + 
        TableInformationView.BaseTableName + 
        TableInformationView.ColumnName 
    having COUNT(*) > 1
)

Edit: != Sign was corrected, but this doesn't make any difference.

Can anybody provide me a solution so that the extra unneeded row representation of the column is not included.

Thank you.

Why do you join onto INFORMATION_SCHEMA.TABLE_CONSTRAINTS twice? — Martin Smith
– Martin Smith, Commented Dec 31, 2010 at 19:55
The second one is to find out the parent table of a relationship. — Nick Binnet
– Nick Binnet, Commented Dec 31, 2010 at 20:01

Chandu · Accepted Answer · 2010-12-31 20:20:38Z

1

If you are looking for a query to get unique table,column combinations, you can use this query:

SELECT *
  FROM (
        SELECT a.*, 
               ROW_NUMBER() OVER(PARTITION BY a.SchemaName,a.BaseTableName,a.ColumnName ORDER BY TableName DESC) rnk
          FROM TableInformationView  a
                ) a
WHERE rnk = 1

answered Dec 31, 2010 at 20:20

Chandu

83.2k19 gold badges137 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chandu Over a year ago

Maybe I misinterpreted the issue. So is you issue with the View definition or the SELECT Query using the view?

Nick Binnet Over a year ago

The issue is with the view itself.

Collectives™ on Stack Overflow

T-SQL Remove Duplicate Columns on this Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related