Informational Queries

Hello,
Are there any commands that would give infomation on all DBs on a SQL
server? (IE size, location of files, name, that sort of thing?) I have just
been given about twenty SQLservers and due to a person leaving I am hoping to
find ot some information about what is on them
I apprecitate the help.
jj
declare @.dbname sysname
if object_id('tempdb..#dbs')is not null drop table #dbs
select
[name]
into
#dbs
from
master..sysdatabases
while (select count(*) from #dbs)>0 begin
select top 1 @.dbname=[name] from #dbs
exec ('sp_helpdb [' + @.dbname + ']')
delete #dbs where [name]=@.dbname
end
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"John Jarrett" wrote:

> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have just
> been given about twenty SQLservers and due to a person leaving I am hoping to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
|||The system tables will provide you with the appropiate information, but....
the best thing is to query them via the predefined stored procedures, read
about the sp_help% procedures in BOL, thatll help you.
For example sp_helpdb, sp_helpfiles
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"John Jarrett" <JohnJarrett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:11569D6F-1381-4E14-9F1E-C0EFB2ABA943@.microsoft.com...
> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have
> just
> been given about twenty SQLservers and due to a person leaving I am hoping
> to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
|||See sps sp_helpdb and sp_helpfile in BOL.
AMB
"John Jarrett" wrote:

> Hello,
> Are there any commands that would give infomation on all DBs on a SQL
> server? (IE size, location of files, name, that sort of thing?) I have just
> been given about twenty SQLservers and due to a person leaving I am hoping to
> find ot some information about what is on them
> I apprecitate the help.
> --
> jj
sql

Information_Shema as a user in Master database

Hi, All
Recently, I was running a script like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi, All,
I think this is an issue for sql 2005, because the when I open em from db
server, the information_schema user does not show, but I connect it through
a remote machine that installed sql 2005, and the user showed.
same as another machine that did not run the script and did installed sql
2005.
Yifei
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:uVLA%23HI$FHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi, All
> Recently, I was running a script like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via
> group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the
> db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the
> problem?
> any help is appreciated.
> Thanks
> Yifei
>
>

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

Does anyone know where I can see the source SQL for this View?
OR
Does anyone know which fields in which system tables would allow me to
determine whether updates and deletes are cascaded?
> Does anyone know where I can see the source SQL for this View?
EXEC master..sp_helptext 'INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS'

> Does anyone know which fields in which system tables would allow me to
> determine whether updates and deletes are cascaded?
You can also get the cascade options using SQL Server OBJECTPROPERTY
functions.
Hope this helps.
Dan Guzman
SQL Server MVP
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:3C2F64CC-DA03-4FE2-B198-E76DDB203BE1@.microsoft.com...
> Does anyone know where I can see the source SQL for this View?
> OR
> Does anyone know which fields in which system tables would allow me to
> determine whether updates and deletes are cascaded?

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Does anyone know where I can see the source SQL for this View?
OR
Does anyone know which fields in which system tables would allow me to
determine whether updates and deletes are cascaded?> Does anyone know where I can see the source SQL for this View?
EXEC master..sp_helptext 'INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS'
> Does anyone know which fields in which system tables would allow me to
> determine whether updates and deletes are cascaded?
You can also get the cascade options using SQL Server OBJECTPROPERTY
functions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:3C2F64CC-DA03-4FE2-B198-E76DDB203BE1@.microsoft.com...
> Does anyone know where I can see the source SQL for this View?
> OR
> Does anyone know which fields in which system tables would allow me to
> determine whether updates and deletes are cascaded?

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 there any way to find out the stored procedure output paramters like I
can findout the input params through following query but I want to findout
the output params list...
SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
thanks in advance.
select
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
PARAMETER_MODE,
NUMERIC_PRECISION,
NUMERIC_SCALE
from INFORMATION_SCHEMA.PARAMETERS
where specific_name = @.chvProcName
order by ordinal_position
look for PARAMETER_MODE it will have a value of OUT
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> Is there any way to find out the stored procedure output paramters like I
> can findout the input params through following query but I want to findout
> the output params list...
> SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
> thanks in advance.
>
>
>
|||No, I mean ... let's assume this is the stored procedure right ... I need
the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
Time...
CREATE PROCEDURE [dbo].[SPSelectTotalServices]
@.ModalityTypeID INT = NULL,
@.LHIN_Code INT = NULL,
@.Lan CHAR(2) = 'EN'
AS
IF (@.Lan = 'EN')
BEGIN
SELECT
MF_ModalityTypeID AS SiteID,
(
SELECT
MT_Alias
FROM tblModalityType
WHERE MT_ModalityTypeID = MF_ModalityTypeID
) AS Site,
CEILING(sum(MF_Median)) AS 'Median Wait Time',
CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
FROM tblModalityFacility
WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
AND
(@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
AND MF_ModalityTypeID IS NOT NULL
GROUP BY MF_ModalityTypeID
ORDER BY 2
END
GO
Is there any way ?
Thanks in advance.
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...[vbcol=seagreen]
> select
> PARAMETER_NAME,
> DATA_TYPE,
> CHARACTER_MAXIMUM_LENGTH,
> PARAMETER_MODE,
> NUMERIC_PRECISION,
> NUMERIC_SCALE
> from INFORMATION_SCHEMA.PARAMETERS
> where specific_name = @.chvProcName
> order by ordinal_position
> look for PARAMETER_MODE it will have a value of OUT
> http://sqlservercode.blogspot.com/
>
> "Rogers" wrote:
|||Thats not an output parameter but a resultset
You could use sp_helptext and parse thru that
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> No, I mean ... let's assume this is the stored procedure right ... I need
> the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
> Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
> AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
>
|||what are you trying to do with the output?
In any case, look up SET FRMONLY in books on line - that should get you there.
for instance:
SET FMTONLY ON
GO
SELECT *
FROM SPSelectTotalServices
GO
(remember to SET FMTONLY OFF when you're done!)
"Rogers" <Rogers@.mailstuff.com> wrote in message news:O7dV$StvFHA.2932@.TK2MSFTNGP10.phx.gbl...
> No, I mean ... let's assume this is the stored procedure right ... I need the output like SiteID,Site,Median Wait Time, 90% Wait
> Time,Average Wait Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID) AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
sql

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 query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
Simon
AFAIK, you can't use the information_schema views in SQL Server 2000 from a linked server as they
don't exists in each database, they only exist in the master database. Use syscolumns etc instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegro ups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

INFORMATION_SCHEMA.COLUMNS query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
SimonAFAIK, you can't use the information_schema views in SQL Server 2000 from a linked server as they
don't exists in each database, they only exist in the master database. Use syscolumns etc instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

INFORMATION_SCHEMA.COLUMNS query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
SimonAFAIK, you can't use the information_schema views in SQL Server 2000 from a
linked server as they
don't exists in each database, they only exist in the master database. Use s
yscolumns etc instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

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