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
Remisha – Thank you for the query, it’s really handy.
Thanks for the script, you saved me an hour!
Hi,
In addition to this script can i add another column that states what exactly is the data length (not declared length), that means concatenating MAX( DataLength(ColumnName)) function
I wanted to find out what the column data types were for the view sys.databases.
To return the column data types for this view, use Pinal’s Script 2 above, but query “sys.all_columns” instead of “sys.columns”.
To return only the columns for sys.databases, add:
WHERE object_name(c.OBJECT_ID) = ‘sysdatabases’
before the ORDER BY.
Hi Guys,
nice post, I want a result in the following format
TableName
———-
columnName datatype Length
test1 int 4
TableName
—————
columnName datatype Length
test2 int 4
That format matters only if you want to show them in a front end application. Why do you want to have data in such format?
Hi Guys,
Have any idea to alter the system data type.
Exp:
UPDATE INFORMATION_SCHEMA.COLUMNS SET NUMERIC_PRECISION=18,NUMERIC_SCALE=2
WHERE DATA_TYPE = ‘decimal’
i want Search text in any Column of Specify Table ,
I want that text which Column ::
How do I
See if this helps
Hi Pinal,
I want a script which will find all the data types used by all the primary keys of the database. That means list all the primary keys with their data types in a database.
Will you please write a script for this.
Thanks,
Rajib
I was actually lookiing for a way to list the columns and datatypes for User Defined Table Types. This article helped but I still had to do a lot of research before I came up with this:
and was able to do this:
SELECT s.name as ‘schema’
, tt.name as TableName
, c.name AS column_name
,c.column_id
,SCHEMA_NAME(t.schema_id) AS type_schema
,t.name AS type_name
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.precision
,c.scale
,t.user_type_id
,c.object_id
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.table_types tt ON tt.type_table_object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = tt.schema_id
Order by s.name,tt.name, c.column_id
Thanks Ron,
I think your script will list all the columns with data types. I want to refine my requirement that list all the primary key columns with data type.
Rajib
Thank u very much…………..
Good One !!
Have added the NULLABLE condtion for each column!!
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
,’NULLABLE’ = case when c.is_nullable=’true’ then ‘YES’ else ‘NO’ end
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY c.OBJECT_ID;
Nice ya. Found your valuable effort here.
Thanks for this.
Ashok.
hi i want select a date from a filde
if fdat=2012/10/10
i want 20121010
can help me !!!
Hope this will help you ..
select replace(replace(replace(convert(char(4),YEAR(‘2012/10/10’))+’A’+convert(char(4),MONTH(‘2012/10/10’))+’B’+convert(char(4),DAY(‘2012/10/10′)),’A’,”),’B’,”),’ ‘,”)
SELECT REPLACE(‘2012/10/10’, ‘/’, ”)
–Eg:-
SELECT REPLACE(FieldName, ‘/’, ”) FROM TableName
thanx a lot buddy
Except manually , Can i migrate the data belonging to one schema to a different one in same db. Please tell me ASAP.
Can you please explain what the single characters ‘c’, ‘t’ mean below? It’s a copy from this blog.
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;
c and t are the aliases for sys.columns and sys.types respectively. You can see that name asighed in the last 2nd and 3rd row from the bottom.
Hi Pinal, Quite helpful article
Sabah
Thanks @Syed
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.