Find all the varchar() fields in sql server?

Is it possible to find all the varchar() columns in my database?

I am using SQL Server 2008 and would like to get the list in SQL serve开发者_开发问答r management console.

JD.

Yep, this should work:

select * from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'varchar'

Try, this will give varchar and nvarchar, if the character_maximum_length column returns -1 then it is varchar(max) or nvarchar(max)

select * from 
INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE in('varchar','nvarchar')

Another method not limited to Views or User tables that utilizes sys objects instead of information_schema. Multiple questions previously answered regarding usage of sys vs information_schema.

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'varchar' --you can change text to other datatypes
ORDER BY c.OBJECT_ID;
GO

Original source utilized for Sql Server 2005, just executed on Sql Server 2016. Reference : Pinal Dave (https://blog.sqlauthority.com) https://blog.sqlauthority.com/2007/08/09/sql-server-2005-list-all-the-column-with-specific-data-types/