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
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
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.
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???