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 (http://blog.SQLAuthority.com)
Hi
we should not touch the underline table as per microsoft statement. therefore an easy and cheapest way to get the information about the column is by using the information_schema view therefore the above task can be done in this manner
DECLARE @DataType VARCHAR(50)
SELECT @DataType = ‘TEXT’
SELECT * FROM Information_Schema.Columns WHERE Data_Type = @DataType
how can i get the exact size of the column along with column name and datatype
Hi Atul,
You have done a good thing… Congrats..
Regards,
Wilson Gunanithi.J
@Atul,
sys.columns and sys.types are not underline tables. They are views to protect underline tables. Your information is incorrect.
information_schema is undocumented where as Microsoft recommends to use sys.columns and sys.types or any other sys. views.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Why do you think INFORMATION_SCHEMA is undocumented. I do see the information on msdn http://msdn.microsoft.com/en-us/library/ms186778(v=sql.105).aspx. Information schema views seem to be simpler solution, is there any other reason not to use the information schema?
It never ceases to amaze me how much energy people will put into trying to fault someone elses work, when instead, it seems to me, they should be trying to do something of their own.
@Jim Mabey,
I can not agree with you any more.
Thanks,
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Sandesh,
It is ok Pinal, We are here to share our knowledge and grow…thank you so much for your highly useful posts always.
It was more than usefull for me, thx alot
This might a little too late to add another comment to this blog. Anyways better late than never
Jim/Pinal
Why do you have to think that when someone comments on something that you have mentioned, it only means that their only intention is to find fault with you?
It is good that Atul has come forward with another point to the same discussion. Now we all know about these 2 views.
The problem I am having with this wonderful script is it does not go down to View level ie it does not list views – is there a fix to that ?
Thanks
hi, could u please let me know that how can we find the duplicate columns in aprox 30 tables of same schema. based on name.
Need to know the best practices for testing Database schema.
Hmmm, information_schema.columns displays the view columns.
Hello Sir,
I need some help.
Is there any procedure that can alter my columns in the database from chanr to nchar,varchar to nvarchar and text to ntext
Thanks in advance
Hi All
i have a column which is a computed column (when click on Table+ look in column DataType) It showes me Computed. I have no of such columns in my database.
With a SQL statement how do i get list of such columns.
Thanks
HELLO
Is there any procedure that can alter all my columns in the database from int to nvarchar and float to nvarchar
Thanks in advance
need some help in Sqlserver.
I need a script file to change table column datatype from int to bigint when the two tables are associated with foreign key , I try to disable the foreign key in the child table and they try to alter but it did not work
SELECT SysObjects.[Name] as TableName,
SysColumns.[Name] as ColumnName,
SysTypes.[Name] As DataType,
SysColumns.[Length] As Length
FROM
SysObjects INNER JOIN SysColumns
ON SysObjects.[Id] = SysColumns.[Id]
INNER JOIN SysTypes
ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = ‘U’
ORDER BY SysObjects.[Name]
Very handy script. This one goes strait to my favourites list.
Is it possible to apply the same concept to User Stored Procedures and User Defined Functions?
That would also be very nice.
Joaquim Félix,
Do you want to know the parameters or the scripts?
@Madhivanan
Madhivanan sorry for the late reply.
My first idea was, similarly to this post, to determine the User Stored Procedures (USP) and User Defined Functions (UDF) that had a specific data type in it’s parameters.
However, after reading your post, the same idea applied to the variables used inside the code would also be nice, since the main purpose is to replace old or deprecated data types with new or enhanced ones.
Regards.
How to get the x and y value as the length value entered in nVarChar(x) and numeric(x,y). Thanks
I have a question,
In oracle, I can define variable’s datatype using another table’s column datatype in procedure or function.. (see the example) can you help me find it out similar things in sql server 2005? Thanks in an advance.
Example:
eno in emp.empno%type,
name out emp.ename%type,
job out emp.job%type,
salary out emp.sal%type,
location out dept.loc%type
create or replace procedure display (
eno in emp.empno%type,
name out emp.ename%type,
job out emp.job%type,
salary out emp.sal%type,
location out dept.loc%type
)
is
begin
select ename,job,sal,loc into name,job,salary,location from emp e,dept d
where e.deptno=d.deptno AND empno=eno;
end;
what is the datatype for email in sql 2005???
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
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
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
[...] years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which [...]
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.