How to List the Source Table Name of Columns in a View (SQL Server 2005)

How to list the source table name of columns in a VIEW (SQL Server 2005)

This information is available from the INFORMATION_SCHEMA views:

SELECT * 
FROM    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS cu
JOIN    INFORMATION_SCHEMA.COLUMNS AS c
ON      c.TABLE_SCHEMA  = cu.TABLE_SCHEMA
AND     c.TABLE_CATALOG = cu.TABLE_CATALOG
AND     c.TABLE_NAME    = cu.TABLE_NAME
AND     c.COLUMN_NAME   = cu.COLUMN_NAME
WHERE   cu.VIEW_NAME    = '<your view name>'
AND     cu.VIEW_SCHEMA  = '<your view schema>'

If your view includes tables from more than one database, the query will become considerably more complex

SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Just replace YourTableName with your actual table name - works for SQL Server 2005 and up.

In case you are using schemas, replace YourTableName by YourSchemaName.YourTableName where YourSchemaName is the actual schema name and YourTableName is the actual table name.

Use TSql to find the original table that a column within a view came from

Execute sp_describe_first_result_set with @browse_information_mode = 1. This will return a result set with the underlying table name and column name of each view column along with other meta-data. Source information will be NULL for view columns derived from expressions but other meta-data (e.g. data type information) will be included.

EXEC sp_describe_first_result_set
      @tsql=N'SELECT * FROM dbo.YourView;'
    , @params = NULL
    , @browse_information_mode = 1;

Note that sp_describe_first_result_set was introduced in SQL Server 2012 so this will not work in prior versions of SQL Server.

How to search a column in multiple tables in SQL Server 2005

You can do:

SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%columnName%'

Sql Query to list all views in an SQL Server 2005 database

To finish the set off (with what has already been suggested):

SELECT * FROM sys.views

This gives extra properties on each view, not available from sys.objects (which contains properties common to all types of object) or INFORMATION_SCHEMA.VIEWS. Though INFORMATION_SCHEMA approach does provide the view definition out-of-the-box.

SQL Server (2005+) query to return the base table and base column (field) for each column (field) in a view

There are few tables in the information schema that you can use to deduce the information. A basic query that gives you quite a bit of data will be:

select * 
from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE v
    inner join INFORMATION_SCHEMA.COLUMNS v1
        on  v.VIEW_NAME=v1.TABLE_NAME and v.COLUMN_NAME=v1.COLUMN_NAME
where v.VIEW_NAME='My_View'

The tables in the information schema are:

select * from INFORMATION_SCHEMA.VIEWS
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

So try using these.

However it works only when you use columns directly from the base tabbles without any formula ar deriving.

Find the real column name of an alias used in a view?

Given this view:

CREATE VIEW viewTest
AS
SELECT
    books.id,
    books.author,
    Books.title AS Name
FROM
    Books

What I can see you can get the columns used and the tables used by doing this:

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns 
WHERE UsedColumns.VIEW_NAME='viewTest'

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE AS UsedTables 
WHERE UsedTables.VIEW_NAME='viewTest'

This is for sql server 2005+. See reference here

Edit

Give the same view. Try this query:

SELECT
    c.name AS columnName,
    columnTypes.name as dataType,
    aliases.name as alias
FROM 
sys.views v 
JOIN sys.sql_dependencies d 
    ON d.object_id = v.object_id
JOIN .sys.objects t 
    ON t.object_id = d.referenced_major_id
JOIN sys.columns c 
    ON c.object_id = d.referenced_major_id 
JOIN sys.types AS columnTypes 
    ON c.user_type_id=columnTypes.user_type_id
    AND c.column_id = d.referenced_minor_id
JOIN sys.columns AS aliases
    on c.column_id=aliases.column_id
    AND aliases.object_id = object_id('viewTest')
WHERE
    v.name = 'viewTest';

It returns this for me:

columnName  dataType  alias

id          int       id
author      varchar   author
title       varchar   Name

This is also tested in sql 2005+

---

Related Topics