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)
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.
@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
In my example, view name is V1. Please change based on your server.
this script throws an error, can you resolve it please.
What’s the error and what’s the SQL version?
Can anyone script it again or resolve the above script please
thank you!!
Ravi, can you please give me more details on the error. I will surely look into it.
Hi,
How to get column Name and data from the table. Please advise.
example
ID 1
Name 2
Address 3
If you are looking for output like above, then look for PIVOT.
How to get column Name and value in select.
Example
ID 1
Name KKKK
Address 123 Main
Why do you want that way? how would you handle multiple rows?
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.
Then you need to do select from table.
Hi, Can someone please advise on how to list the primary key column data types for all the tables in the entire database ?
ace, thankyou
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..