INFORMATION_SCHEMA.COLUMNS error

Hi All,

For some reason, whenever I execute the following query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

I get a few rows returned then I get the following error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Is my INFORMATION_SCHEMA.COLUMNS view corrupt somehow?

Querries of other INFORMATION_SCHEMA views are fine, just this one is returning an error. Anyone have any ideas about how to fix this?

John

Can you post the table structure..|||

Manivannan.D.Sekaran wrote:

Can you post the table structure..

The table structure of what? The INFORMATION_SCHEMA.COLUMNS view? This is the default INFORMATION_SCHEMA.COLUMNS view that is created by default in SQL Server. Its what should be there by default. Its not a view that I've created.

So what table structure are you referring to?

John

|||

Ohh yes.. You dont know which table cause the problem.. Simply forget..

Ok try to execute the following query..

At one time you will get an error.. So that is the table causing the issue... Then post the DDL of that table...

sp_msforeachtable'Print ''? is Executing..'';Select Count(Column_Name) TotalColumn, ''?'' TableName fromINFORMATION_SCHEMA.COLUMNS Where object_id(table_name) = object_id(''?'');Print ''? is Completed..'';'

|||

Hi Manivannan,

Ok I executed your query. I didn't get any errors.

I think the issue is with the view in general on my server. I get the error when I execute the query against INFORMATION_SCHEMA.COLUMNS regardless of the database I execute against.

When I execute your script against ALL of my databases (on my server) I get no errors.

|||

Here's a little background on what I think may have caused my server to be in such a state.

I have been tasked with changing all columns, in all tables, in our database that are of varchar type to be nvarchar. I immediately issued a query such as

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='varchar'

That very simply returned ALL columns in my database that were of varchar type, I think there were hundreds of rows returned. So, I think this is what screwed things up for me, I executed an update statement against that view as a test to see if I could do such a thing. This is the update statement I executed:

UPDATE INFORMATION_SCHEMA.COLUMNS SET DATA_TYPE='nvarchar' WHERE DATA_TYPE='varchar'

I figured something was wrong when upon executing that statement it said that 1 row was updated. I got no error message, but now all querries of that view yields the error I documented.

|||

Its really worst idea changing the system tables directly. You should use the proper alter statement.

The update query you performed, is modified the data in System Table (spt_datatype_info) rather than your original table (on syscolumns).

If you only executed the above query then use the following query to revert back spt_datatype_info into original state..

UPDATE spt_datatype_info

SET local_type_name=type_name

WHERE local_type_name='varchar'

|||

WooHoo, it worked!

I changed your update statement slightly to:

UPDATE spt_datatype_info
SET local_type_name=type_name
WHERE local_type_name!=type_name

It restored all that were messed up (there were two rows actually, the row for "varchar" and the row for "text").

Thank you very much Manivannan!

John

sql