SQL SERVER – 2005 – List All The Column With Specific Data Types

Since we upgraded to SQL Server 2005 from SQL Server 2000, we have used following script to find out columns with specific datatypes many times. It is very handy small script.

SQL Server 2005 has new datatype of VARCHAR(MAX), we decided to change all our TEXT datatype columns to VARCHAR(MAX). The reason to do that as TEXT datatype will be deprecated in future version of SQL Server and VARCHAR(MAX) is superior to TEXT datatype in features. We run following script to identify all the columns which are TEXT datatype and developer converts them to VARCHAR(MAX)

Script 1 : Simple script to identify all the columns with datatype TEXT in specific database
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 = 'text' --you can change text to other datatypes
ORDER BY c.OBJECT_ID;
GO

Script 2 : Extended script to identify all the columns datatype and other information in specific database
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY c.OBJECT_ID;

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – 2005 – SSMS – Enable Autogrowth Database Property
Next Post
SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

Related Posts

65 Comments. Leave new

  • Hi,
    Can you help me with this
    How to script out the views in a specific database with columns and datatypes.

    Thanks.

    Reply
    • @Ravi

      SELECT
      clmns.name AS [Name],
      CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
      CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
      usrt.name AS [DataType],
      ISNULL(baset.name, N”) AS [SystemType],
      CAST(CASE WHEN baset.name IN (N’nchar’, N’nvarchar’) AND clmns.max_length -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
      CAST(clmns.precision AS int) AS [NumericPrecision],
      CAST(clmns.scale AS int) AS [NumericScale],
      clmns.is_nullable AS [Nullable],
      clmns.is_computed AS [Computed],
      ISNULL(s2clmns.name, N”) AS [XmlSchemaNamespaceSchema],
      ISNULL(xscclmns.name, N”) AS [XmlSchemaNamespace],
      ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
      CAST(clmns.is_sparse AS bit) AS [IsSparse],
      CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
      clmns.column_id AS [ID]
      FROM
      sys.all_views AS v
      INNER JOIN sys.all_columns AS clmns ON clmns.object_id=v.object_id
      LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
      LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
      LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
      LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
      LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
      LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
      WHERE
      (v.type = ‘V’)and(v.name=’V1′ and SCHEMA_NAME(v.schema_id)=’dbo’)
      ORDER BY
      [ID] ASC

      Reply
  • Hi,
    How to get column Name and data from the table. Please advise.
    example
    ID 1
    Name 2
    Address 3

    Reply
  • How to get column Name and value in select.
    Example
    ID 1
    Name KKKK
    Address 123 Main

    Reply
  • SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = ‘ABC’ AND TABLE_SCHEMA = ‘XYZ’
    i am getting column name from the above query.
    But i want to get the value for each column using the select statement. Please advise.

    Reply
  • Hi, Can someone please advise on how to list the primary key column data types for all the tables in the entire database ?

    Reply
  • ace, thankyou

    Reply
  • Hi Pinal, Great work.. I need a boolean value for primary key along with the columns datatype and other information in specific database..meaning with the column information, i would like to know if it is a primary key or not. I want it for all the columns in the database, not just based on a table..

    Reply

Leave a Reply

Menu