INFORMATION_SCHEMA: Probably bug in SQL-2005

Try to run this script, first in any database under SQL-2000, then in SQL-2005:

select COLUMN_DEFAULT from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_DEFAULT IS NOT NULL

In SQL-2000 values of the defaults are in single pair of brackets:

COLUMN_DEFAULT

('')
(0)
('')
(0)
(0)
(getdate())
(db_name())
(1)
(21)
(N'anonymous')

In SQL-2005 integer values are in double brackets, and all other defaults - in single brackets:

COLUMN_DEFAULT

('')
((0))
('')
((0))
((0))
(getdate())
(db_name())
((1))
((21))
(N'anonymous')

At the same time, OpenSchema method of ADO:

Connection.OpenSchema(adSchemaColumns)

returns different defaults in SQL2000 and SQL2005. In SQL2000 column defaults are in useful form, without any brackets:

'', 0, getdate()

But in SQL2005 they are enclosed in single brackets:

(''), (0), (getdate())

Does anybody know this problem? Is it normal, or it's a bug?

SQL Server 2005 normalizes expressions in defaults, check constraints and computed columns. As a result of this, the expression will look different from how it was stored in SQL Server 2000. However a particular expression will look the same irrespective of how you write it (extra spaces, paranthesis etc). This is documented in the following BOL topic:

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

|||

Now I understand!

Thanks!

INFORMATION_SCHEMA.SCHEMATA does not return all rows on SQL 2005

Hi,
In SQL 2000, the following query used to return all the database names:
SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
However, in SQL 2005, it just returns "master" as the database (that
too a number of times).
Can someone please confirm if this is a bug in SQL 2005?
Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
standard statements.
Thank you in advance for your help.
Pradeep> In SQL 2000, the following query used to return all the database names:
Which is incorrect behavior. This is fixed in SQL Server 2005 (this should
never have been a list of databases).
To get a list of database names,
SELECT name FROM sys.databases|||To add on to Aaron's response, the reason for the SQL 2005 change was to
make the INFORMATION_SCHEMA.SCHEMATA view consistent with the ANSI standard.
The SQL 2000 behavior (database list) was proprietary. This is listed in
the SQL 2005 Books Online under the breaking changes topic
<ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/47edefbd-a09b-4087-937a
-453cd5c6e061.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pradeep" <pradeep@.tapadiya.net> wrote in message
news:1136602519.887257.199350@.g43g2000cwa.googlegroups.com...
> Hi,
> In SQL 2000, the following query used to return all the database names:
> SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
> However, in SQL 2005, it just returns "master" as the database (that
> too a number of times).
> Can someone please confirm if this is a bug in SQL 2005?
> Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
> standard statements.
> Thank you in advance for your help.
> Pradeep
>

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
ChrisIt's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chris

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
ChrisIt's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
--
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chrissql

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
Chris
It's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chris

INFORMATION_SCHEMA.PARAMETERS

Is anybody able to access system tables used by INFORMATION_SCHEMA.PARAMETERS?

Namely: sys.sysscalartypes, sys.sysschobjs and sys.syscolpars.

A select on any of these failes with 'Invalid object name'

These are system base tables that are used by the database engine only.

Please see http://msdn2.microsoft.com/en-us/library/ms179503.aspx

Is there a reason why you would want to access these base tables ?

|||Curiosity really...

INFORMATION_SCHEMA.PARAMETERS

Is anybody able to access system tables used by INFORMATION_SCHEMA.PARAMETERS?

Namely: sys.sysscalartypes, sys.sysschobjs and sys.syscolpars.

A select on any of these failes with 'Invalid object name'

These are system base tables that are used by the database engine only.

Please see http://msdn2.microsoft.com/en-us/library/ms179503.aspx

Is there a reason why you would want to access these base tables ?

|||Curiosity really...

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

INFORMATION_SCHEMA Views and Indexed Views

Hi,
I wanted to write some stored procedures to help me manage my indexed views
in SQL2000/2008. Since I wanted to follow the advice to use the
INFORMATION_SCHEMA views instead of system tables/catalog views. I have
this query that will run in both 2000 and 2008 and it correctly finds my
indexed views:
select *
from sysobjects
where type = 'V'
and id in (select id from sysindexes);
However, if I run this query in either 2000 or 2008 , it returns nothing:
select * from INFORMATION_SCHEMA.VIEWS
where TABLE_NAME in (
select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
The SQL 2008 BOL states "Returns one row for each column that is constrained
as a key in the current database.", and SQL 2000 BOL state "Contains one row
for each column, in the current database, that is constrained as a key."
Does anyone have any sage advice on this topic, or should I post it as a
Connect issue for SQL 2008?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>
The INFORMATION_SCHEMA describes only the logical features of the database:
tables, columns, constraints. Not indexes because they are a physical
implementation construct and aren't part of standard SQL like the
INFORMATION_SCHEMA.
For index information you need sys.indexes and sys.index_columns, or
dbo.sysindexes and dbo.sysindexkeys. That's unless the index is one that
supports a constraint, in which case the same information will be in
INFORMATION_SCHEMA.
--
David Portas|||Someone will point out what's going on and for every issue you encounter
they'll tell you how you screwed up (though usually politely - unless you
get celko). I'll probably get blasted for this post. However, personally, I
think INFORMATION_SCHEMA sucks and that the SQL Server catalog tables are
far, far superior and thought out much better (esp. 2005).
While I'll blame Microsoft for the poor docs on it, I don't blame them for
the bulk of my issues with it as they just implimented the ANSI standard. I
just don't like it and don't think it was well thought out.
Jay
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>

Information_Schema Question

Hi
I am selecting some column info from information_schema like this:
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
FROM test.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test Table';
I am doing this via OleDb connection to Sql2000
I connect as the owner of the database/tables.
I connect to the database for which I am seeking the information
('test').
I get rows returned for some tables, but not others. At first, when I
test in query analyzer (connected as same user) I got the same
results. So then, I went to Enterprise Manager and looked at
permissions for the tables for which I was getting no data returned.
There were no permissions on the tables at all. If I added, just/only
SELECT permission for this user, then I get results in query analyzer.
Ah ha, I think, I have it! So now I go back to my application and try
again with the OleDb connection and, well, it works for some tables
now (for which it did not earlier) , but there are still other tables
that , although they now return column information in Query Analyser,
still do not return any column information from my OleDb connection
query.
Is this a permissions issue with the table itself?
What else should I be looking for in my database that could be
preventing this column information from being returned to the user via
the connection? Is it the connection?
Thanks
JeffNobody has any idea what I might be missing here?
Jeff
On Thu, 22 Dec 2005 06:58:10 GMT, Jeff User <jeff31162@.hotmail.com>
wrote:
>Hi
>I am selecting some column info from information_schema like this:
>SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
>FROM test.INFORMATION_SCHEMA.COLUMNS
>WHERE TABLE_NAME = N'Test Table';
>I am doing this via OleDb connection to Sql2000
>I connect as the owner of the database/tables.
>I connect to the database for which I am seeking the information
>('test').
>I get rows returned for some tables, but not others. At first, when I
>test in query analyzer (connected as same user) I got the same
>results. So then, I went to Enterprise Manager and looked at
>permissions for the tables for which I was getting no data returned.
>There were no permissions on the tables at all. If I added, just/only
>SELECT permission for this user, then I get results in query analyzer.
>Ah ha, I think, I have it! So now I go back to my application and try
>again with the OleDb connection and, well, it works for some tables
>now (for which it did not earlier) , but there are still other tables
>that , although they now return column information in Query Analyser,
>still do not return any column information from my OleDb connection
>query.
>Is this a permissions issue with the table itself?
>What else should I be looking for in my database that could be
>preventing this column information from being returned to the user via
>the connection? Is it the connection?
>Thanks
>Jeff

information_schema question

Hi
I wanted to know what is the object type for information_schema. I could
not locate this object in any database.
Please share if anyone knows.
on all DB's
select * from sysobjects where lower(name)='information_schema'
Tks
Mangesh"INFORMATION_SCHEMA" isn't the name of an object, it's the name of a schema -
the namespace that contains the set of info schema views. You'll find that
the views are actually defined in Master.
--
David Portas
SQL Server MVP
--
"Mangesh Deshpande" wrote:
> Hi
> I wanted to know what is the object type for information_schema. I could
> not locate this object in any database.
> Please share if anyone knows.
> on all DB's
> select * from sysobjects where lower(name)='information_schema'
> Tks
> Mangesh

Information_Schema Question

Hi
I am selecting some column info from information_schema like this:
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_
LENGTH,IS_NULLABLE
FROM test.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test Table';
I am doing this via OleDb connection to Sql2000
I connect as the owner of the database/tables.
I connect to the database for which I am seeking the information
('test').
I get rows returned for some tables, but not others. At first, when I
test in query analyzer (connected as same user) I got the same
results. So then, I went to Enterprise Manager and looked at
permissions for the tables for which I was getting no data returned.
There were no permissions on the tables at all. If I added, just/only
SELECT permission for this user, then I get results in query analyzer.
Ah ha, I think, I have it! So now I go back to my application and try
again with the OleDb connection and, well, it works for some tables
now (for which it did not earlier) , but there are still other tables
that , although they now return column information in Query Analyser,
still do not return any column information from my OleDb connection
query.
Is this a permissions issue with the table itself?
What else should I be looking for in my database that could be
preventing this column information from being returned to the user via
the connection? Is it the connection?
Thanks
JeffNobody has any idea what I might be missing here?
Jeff
On Thu, 22 Dec 2005 06:58:10 GMT, Jeff User <jeff31162@.hotmail.com>
wrote:

>Hi
>I am selecting some column info from information_schema like this:
>SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_
LENGTH,IS_NULLABLE
>FROM test.INFORMATION_SCHEMA.COLUMNS
>WHERE TABLE_NAME = N'Test Table';
>I am doing this via OleDb connection to Sql2000
>I connect as the owner of the database/tables.
>I connect to the database for which I am seeking the information
>('test').
>I get rows returned for some tables, but not others. At first, when I
>test in query analyzer (connected as same user) I got the same
>results. So then, I went to Enterprise Manager and looked at
>permissions for the tables for which I was getting no data returned.
>There were no permissions on the tables at all. If I added, just/only
>SELECT permission for this user, then I get results in query analyzer.
>Ah ha, I think, I have it! So now I go back to my application and try
>again with the OleDb connection and, well, it works for some tables
>now (for which it did not earlier) , but there are still other tables
>that , although they now return column information in Query Analyser,
>still do not return any column information from my OleDb connection
>query.
>Is this a permissions issue with the table itself?
>What else should I be looking for in my database that could be
>preventing this column information from being returned to the user via
>the connection? Is it the connection?
>Thanks
>Jeff

information_schema question

Hi
I wanted to know what is the object type for information_schema. I could
not locate this object in any database.
Please share if anyone knows.
on all DB's
select * from sysobjects where lower(name)='information_schema'
Tks
Mangesh"INFORMATION_SCHEMA" isn't the name of an object, it's the name of a schema
-
the namespace that contains the set of info schema views. You'll find that
the views are actually defined in Master.
David Portas
SQL Server MVP
--
"Mangesh Deshpande" wrote:

> Hi
> I wanted to know what is the object type for information_schema. I could
> not locate this object in any database.
> Please share if anyone knows.
> on all DB's
> select * from sysobjects where lower(name)='information_schema'
> Tks
> Mangeshsql

information_schema question

Hi
I wanted to know what is the object type for information_schema. I could
not locate this object in any database.
Please share if anyone knows.
on all DB's
select * from sysobjects where lower(name)='information_schema'
Tks
Mangesh
"INFORMATION_SCHEMA" isn't the name of an object, it's the name of a schema -
the namespace that contains the set of info schema views. You'll find that
the views are actually defined in Master.
David Portas
SQL Server MVP
"Mangesh Deshpande" wrote:

> Hi
> I wanted to know what is the object type for information_schema. I could
> not locate this object in any database.
> Please share if anyone knows.
> on all DB's
> select * from sysobjects where lower(name)='information_schema'
> Tks
> Mangesh

Information_Schema Question

Hi
I am selecting some column info from information_schema like this:
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_ NULLABLE
FROM test.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test Table';
I am doing this via OleDb connection to Sql2000
I connect as the owner of the database/tables.
I connect to the database for which I am seeking the information
('test').
I get rows returned for some tables, but not others. At first, when I
test in query analyzer (connected as same user) I got the same
results. So then, I went to Enterprise Manager and looked at
permissions for the tables for which I was getting no data returned.
There were no permissions on the tables at all. If I added, just/only
SELECT permission for this user, then I get results in query analyzer.
Ah ha, I think, I have it! So now I go back to my application and try
again with the OleDb connection and, well, it works for some tables
now (for which it did not earlier) , but there are still other tables
that , although they now return column information in Query Analyser,
still do not return any column information from my OleDb connection
query.
Is this a permissions issue with the table itself?
What else should I be looking for in my database that could be
preventing this column information from being returned to the user via
the connection? Is it the connection?
Thanks
Jeff
Nobody has any idea what I might be missing here?
Jeff
On Thu, 22 Dec 2005 06:58:10 GMT, Jeff User <jeff31162@.hotmail.com>
wrote:

>Hi
>I am selecting some column info from information_schema like this:
>SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_ NULLABLE
>FROM test.INFORMATION_SCHEMA.COLUMNS
>WHERE TABLE_NAME = N'Test Table';
>I am doing this via OleDb connection to Sql2000
>I connect as the owner of the database/tables.
>I connect to the database for which I am seeking the information
>('test').
>I get rows returned for some tables, but not others. At first, when I
>test in query analyzer (connected as same user) I got the same
>results. So then, I went to Enterprise Manager and looked at
>permissions for the tables for which I was getting no data returned.
>There were no permissions on the tables at all. If I added, just/only
>SELECT permission for this user, then I get results in query analyzer.
>Ah ha, I think, I have it! So now I go back to my application and try
>again with the OleDb connection and, well, it works for some tables
>now (for which it did not earlier) , but there are still other tables
>that , although they now return column information in Query Analyser,
>still do not return any column information from my OleDb connection
>query.
>Is this a permissions issue with the table itself?
>What else should I be looking for in my database that could be
>preventing this column information from being returned to the user via
>the connection? Is it the connection?
>Thanks
>Jeff

INFORMATION_SCHEMA query question: constraint columns

Hi Folks:

I'm a little new to SQLServer, so please pardon my ignorance!

I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
TABLE_CONSTRAINTS. I'm looking for the views that will give me the list of
columns by constraint.

For instance, if Table1 has a unique key called Table1_UK01, I can find that
under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know the
columns in that UK constraint. I've tried
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this user
table doesn't seem to show up in either of those views.

Can anyone point me in the right direction? Any sample queries would be
tremendously appreciated. I'm going to be using this meta-data to
automatically generate quite a bundle of stored procs that do updates based
on finding rows via unique keys...

TIA,
DaveUnique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
constraint or an index? Use constraints and there shouldn't be a problem.
There is no physical difference between a unqiue constraint and a unique
index.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<vpGdnZ-5heemC1Pd4p2dnA@.giganews.com>...
> Unique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
> KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
> constraint or an index? Use constraints and there shouldn't be a problem.
> There is no physical difference between a unqiue constraint and a unique
> index.

Yes, these are declared as constraints, not just indexes.

I think I've figured out the problem, but I don't know how to fix it.
The user tables are all owned by a user we created called "dw". The
docs say that these views return info about objects the current user
has access to. If I select current_user, I get "dbo". I notice that
the information_schema.constraint_column_usage only returns info about
constraints where the table is owned by dbo.

When I connect, I'm connecting (in Query Analyzer, for instance) as
user dw, but if I immediately select current_user, it shows me "dbo".
I'd assume if I can connect as "dw" rather than "dbo", I'll actually
see the constraint_column_usage meta-data for tables owned by "dw"
rather than "dbo".

So, how do I "get connected" as the user "dw" rather than "dbo".
Logging in as SQLServer authenticated user "dw" obviously isn't doing
the trick. Is there some sort of ALTER statement to change my
current_user? (This is SQLServer 7.0, btw).

TIA!
Dave|||Dave Sisk (dsisk@.nc.rr.com.0nospam0) writes:
> I'm a little new to SQLServer, so please pardon my ignorance!
> I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
> TABLE_CONSTRAINTS. I'm looking for the views that will give me the list
> of columns by constraint.
> For instance, if Table1 has a unique key called Table1_UK01, I can find
> that under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to
> know the columns in that UK constraint. I've tried
> INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this
> user table doesn't seem to show up in either of those views.
> Can anyone point me in the right direction? Any sample queries would be
> tremendously appreciated. I'm going to be using this meta-data to
> automatically generate quite a bundle of stored procs that do updates
> based on finding rows via unique keys...

Rather than getting lost in the maze of the INFORMATION_SCHEMA views,
access the system tables directly. You will need to do that anyway if
you need information about indexes that are not constraints. Here is a
query:

SELECT i.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
AND ik.colid = c.colid
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND o.name = 'accountstats'
AND o.uid = USER_ID('dw')
ORDER BY i.name, ik.keyno

Gives you all indexes and their columns for this table. (It's possible
to constrain it to only unique constriaints, but I'm too lazy for that
now. Hint is that Unique constratins live in sysobjects too, and with
a parentobj = the object id for the table.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement = 'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
--
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>sql

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
Simon
Only with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan
|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon
|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegro ups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

INFORMATION_SCHEMA permissions

Hi I need to see all the indexes in a database. The ID has dbo rights
to the database, but not to the master. I can't see anything in
INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
An sa ID for the master sees everything however.
Thanks for your help
PachydermitisI believe your user needs to be in the ddl_admin role?
"Pachydermitis" <dedejavu@.hotmail.com> wrote in message
news:4f954dcf.0309190903.6fbdfc77@.posting.google.com...
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> An sa ID for the master sees everything however.
> Thanks for your help
> Pachydermitis