sql-server

How to get the datatype of a column of a view in SQL Server

I want to get the datatype of a column of a view in SQL Server. Is there an efficient way to do that?

I have to get the Database Name, Schema, View Name all dynamically from one database, look for the view in another database and find the data type of the column in the third database.

E.g.

SELECT @db2 = Name FROM db1.schema.databases
SELECT @c = Name FROM db1.schema.columns
SELECT @v = Name FROM db1.schema.views


SELECT @datatype = query to get {datatype} of column {c} from {db2}.{schema}.{v}

Here column {c} of {db2}.{schema}.{v} can refer another database say {db3}

Please suggest.

Solution

  • Don't know exactly what you need, but this might help you:

    CREATE VIEW dbo.TestView AS
SELECT * FROM master..spt_values;
GO


--This is the view's output
SELECT * FROM dbo.TestView;
GO


--Set your variables
DECLARE @db2 VARCHAR(100) = 'master';
DECLARE @c  VARCHAR(100) = 'type';
DECLARE @vSchema  VARCHAR(100) = 'dbo';
DECLARE @vName VARCHAR(100) = 'TestView' 


--The query will display the `DATA_TYPE` and all other columns returned by `INFORMATION_SCHEMA.COLUMNS`
SELECT c.DATA_TYPE
        ,c.* 
FROM master.INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME=@vName 
    AND c.TABLE_SCHEMA=@vSchema
    AND c.COLUMN_NAME=@c
    AND c.TABLE_CATALOG=@db2; --forgot this in the first post...


--Clean-Up
GO
DROP VIEW dbo.TestView;

    It's a bit fuzzy, that the COLUMNS view returns tables and views as if they were the same. The advantage: You can use the same approach to check a table's column...

    Hint: This INFORMATION_SCHEMA.COLUMNS is just a built-in view looking into the corresponding sys tables.