CODEWITHSUNDEEP
sql-server
Sonu K
Sonu K

Reputation: 2812

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.

Upvotes: 2

Views: 12991

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

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

USE master;
GO

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.

Upvotes: 4

Related Questions