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

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

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
Pachydermitis[posted and mailed, please reply in news]

Pachydermitis (dedejavu@.hotmail.com) writes:
> 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.

Don't really see where INFORMATION_SCHEMA comes in. There is no
information about indexes there. Generally, I prefer using the
system tables, because they hold the complete set of information.

To see all indexes in a database (save those on system tables):

SELECT "table" = object_name(id), name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY "table", name

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||dedejavu@.hotmail.com (Pachydermitis) wrote in message news:<4f954dcf.0309190902.1d421bf4@.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

Hi,

This would give you the info what you are seeking..

select name, object_name(id) from sysindexes

-Manoj|||Manoj Rajshekar (manrajshekar@.yahoo.com) writes:
> This would give you the info what you are seeking..
> select name, object_name(id) from sysindexes

And a lot more he is not interested in. He will also get the names of
heap tables (tables without clustered indexes), hypothetcial indexes,
statistics and location of text data. This SELECT filters this kind
of information:

SELECT "table" = object_name(id), name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY "table", name

and also filters indexes on system tables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
I have been able to get the TableName and IndexNames (along with a few
I don't want _WA_. . .) but I can't seem to get the column names or
get rid of the _WA_ ones.

I was trying to get TableName, IndexName, ColumnName

SELECT obj.[name],ind.[name] FROM sysindexes ind
inner join sysobjects obj on ind.[id]=obj.[id]
ORDER BY obj.[name],ind.indid

Thanks again
Pachydermitis|||Pachydermitis (dedejavu@.hotmail.com) writes:
> I have been able to get the TableName and IndexNames (along with a few
> I don't want _WA_. . .) but I can't seem to get the column names or
> get rid of the _WA_ ones.

Had you used the query I suggested, you would have been relieved from the
_WA "indexes". (Which are statistics and hypothetical indexes.)

> I was trying to get TableName, IndexName, ColumnName

Here is a query that gives this. For multi-column indexes you get one
row per index. If you want all columns for an index on one line, you
will have run some iteration.

SELECT "table" = object_name(i.id), i.name,
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i.id, name, 'IsHypothetical') = 0
AND indexproperty(i.id, name, 'IsStatistics') = 0
AND indexproperty(i.id, name, 'IsAutoStatistics') = 0
AND objectproperty(i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustered" DESC, i.name, ik.keyno

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

INFORMATION_SCHEMA on another database

I want to get the max lenght of a column on a table in anther database. I
have a linked server pointing to that database. INFORMATION_SCHEMA will not
work as it reports only works on the current database.
How can I do this.
Thanks
kevinJust tried this on my server, using a four-part name to reference the table:
SELECT linked_srv.catalog.information_schema.[columns]
Worked fine. Is the other database a SQL server db?
"kevin" wrote:

> I want to get the max lenght of a column on a table in anther database. I
> have a linked server pointing to that database. INFORMATION_SCHEMA will n
ot
> work as it reports only works on the current database.
> How can I do this.
> Thanks
> kevin|||Perhaps you can use stored procedures like below for the remote server?
sp_catalogs
sp_linkedservers
sp_indexes
sp_primarykeys
sp_foreignkeys
sp_tables_ex
sp_columns_ex
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:AAB87C6E-97BF-4039-A93D-F072284EC25E@.microsoft.com...
>I want to get the max lenght of a column on a table in anther database. I
> have a linked server pointing to that database. INFORMATION_SCHEMA will n
ot
> work as it reports only works on the current database.
> How can I do this.
> Thanks
> kevin|||Mark;
when connected to MyLocalServer, if I execute this:
SELECT *
from MyLinkedServer.MyLinkedDB.information_schema.[columns]
I get
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'MyLinkedServer' does not contain table
'"MyLinkedDB"."information_schema"."columns"'. The table either does not
exist or the current user does not have permissions on that table.
from MyLinkedServer I get what I expect.
I know that the linked server is set up properly because I have SP's running
.
The linked server is using
"Mark Williams" wrote:
> Just tried this on my server, using a four-part name to reference the tabl
e:
> SELECT linked_srv.catalog.information_schema.[columns]
> Worked fine. Is the other database a SQL server db?
> "kevin" wrote:
>|||In 2000, the info schema views only exists physically in the master database
, which is most likely
why you get this error. Use the system tables or the system stored procedur
es I posted in the other
post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:1E532DC4-6127-433D-972A-1C0E134AA9F2@.microsoft.com...
> Mark;
> when connected to MyLocalServer, if I execute this:
> SELECT *
> from MyLinkedServer.MyLinkedDB.information_schema.[columns]
> I get
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'MyLinkedServer' does not contain table
> '"MyLinkedDB"."information_schema"."columns"'. The table either does not
> exist or the current user does not have permissions on that table.
> from MyLinkedServer I get what I expect.
> I know that the linked server is set up properly because I have SP's runni
ng.
> The linked server is using
> "Mark Williams" wrote:
>

INFORMATION_SCHEMA Information

I can use queries to retreive many informations from INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'tablename'
But I want to know how to find where that table exists as it is not
part of sysobjects
MadhivananThe Information Schema consists of views, which you can find in the Master
database.
David Portas
SQL Server MVP
--sql

information_schema for temp table ?

Hi there!

I'm trying to find how can I get the information_schema for a temp table.

I'm trying to find all columns of a temp table.

So it will be something like this SELECT * FROM information_schema.columns

But it doesn't work for temp table, I tried tempdb.dbo.information_schema.columns ... nada...

Please help!

Thanks,

Or Thoi don't know what you are really trying to do but try...

SELECT * FROM #MyTempTable WHERE 1 = 0 will give the column names.

I know I do not want know the answer but why do you not know the structure of the temp table?|||I don't know the structure of my temp table because I use pivot tables.

And I transfer my data into an Excel worksheet using VB6. And when I do this, I loose all my columns name.

So it's a real pain in the a**.|||I don't know the structure of my temp table because I use pivot tables.

I am not sure why this matters. Store them in an array of variables and transfer them out too. ReDim sucks I know.

And I transfer my data into an Excel worksheet using VB6. And when I do this, I loose all my columns name.

it aint on the resume no more but i did some VB6 once upon a time. how are are you doing the export? There are a few ways to do this. Recently there was a thread here and there is some info on sqlteam about how to BCP out column names. Have you thought about using BCP?

However I am guessing you are doing the old Open #1 FOR OUTPUT or whatever it was or perhaps you are using filesystem objects.|||No I use CopyFromRecordSet of the Excel.Application.

What's BCP by the way ?

I am not sure why this matters. Store them in an array of variables and transfer them out too. ReDim sucks I know.

As I use a date as a pivot the number of columns still increase day by day|||bulk copy program. google it. or filesystem objects. heck google SQL Server DTS. Or "Visual Basic 6 Open file". I even bet if you check your VBA documentaion, you will find a way to do this. There are many ways to skin a cat.|||I did google the bulk copy, which is very intresting feature of sql.

I also checked at the vba documentions and no body mention how to do that or if it's doable... anyways ...

I'll try to find a way...

Thanks|||I found the best way to do it by myself, it was so easy lol... shame on me.|||enlighten us.|||As i said, I use it in a VB6 app that I made, and the results of the stored proc is stored in a record set so I build an array like this

array(#)= rs.fields(#).name...

It was SOOOO simple...

anyway... thank you all

Information_Schema disappears?

Recently we had a development server on WIN2K running SQL 2K. We noticed that the Information_Schema views disappeared. We have no idea how. It appeared to be about the time most recent patches were run. However, patches were applied to many servers, and the others all have Information_Schema
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and reinstalled SQL2K. All patches were installed on both the operating system and sql
Information_Schema was present
We have not reinstalled
We have not rebooted
Information_Schema is now not present
Last week's queries against it now fail
Any ideas?It appears that the views are still in Master, but have owner dbo
They can only be accessed in Master, and don't have data for other db's
What would cause this
What can be done to correct
Thanks!|||Any ideas on this
It is truly vexing, and we'd appreciate some input here!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.

Information_Schema disappears?

Recently we had a development server on WIN2K running SQL 2K. We noticed t
hat the Information_Schema views disappeared. We have no idea how. It appea
red to be about the time most recent patches were run. However, patches were
applied to many servers, a
nd the others all have Information_Schema.
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and
reinstalled SQL2K. All patches were installed on both the operating system
and sql.
Information_Schema was present.
We have not reinstalled.
We have not rebooted.
Information_Schema is now not present.
Last week's queries against it now fail.
Any ideas?It appears that the views are still in Master, but have owner dbo.
They can only be accessed in Master, and don't have data for other db's.
What would cause this?
What can be done to correct?
Thanks!|||Any ideas on this?
It is truly vexing, and we'd appreciate some input here!!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.

Information_schema and Procedures and logins

Is there an information_schema view to check if a proc / login exist?What is a proc / login?
For stored procedures, you can use information_schema.routines
AMB
"Chedva" wrote:

> Is there an information_schema view to check if a proc / login exist?
>
>|||Not for logins. For stored procedures, check out ROUTINES.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:%23XbQa9mRFHA.244@.TK2MSFTNGP12.phx
.gbl...
> Is there an information_schema view to check if a proc / login exist?
>|||Procedures are in SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE
ROUTINE_TYPE='PROCEDURE'
Logins are not held in information_schema views, you will have to go to
master.dbo.syslogins for that.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:%23XbQa9mRFHA.244@.TK2MSFTNGP12.phx.gbl...
> Is there an information_schema view to check if a proc / login exist?
>|||You can find stored procedures (and user defined functions) in
information_schema.routines. There are views for security
(INFORMATION_SCHEMA.COLUMN_PRIVILEGES and
INFORMATION_SCHEMA.TABLE_PRIVILEGES) but there is no view that lists all the
users in a database or the logins on a server. You will have to use
master..syslogins for that. Or you can use the stored procedure
sp_MShasdbaccess.
Jacco Schalkwijk
SQL Server MVP
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:%23XbQa9mRFHA.244@.TK2MSFTNGP12.phx.gbl...
> Is there an information_schema view to check if a proc / login exist?
>

INFORMATION_SCHEMA and increment

I try to identify the value of increment property of a column.
INFORMATION_SCHEMA does not suppply it.
Somebody know how to get it ?
Thanks
The ANSI standard INFORMATION_SCHEMA views don't expose proprietary
extensions like IDENTITY values. However, you can augment the results using
functions like IDENT_CURRENT. For example:
USE Northwind
SELECT IDENT_CURRENT(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
) AS CurrentIdentity
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'Orders'
Hope this helps.
Dan Guzman
SQL Server MVP
"DigitalGus" <papierCrayon@.hotmail.com> wrote in message
news:KFjkc.25427$k%.581253@.news20.bellglobal.com.. .
> I try to identify the value of increment property of a column.
> INFORMATION_SCHEMA does not suppply it.
> Somebody know how to get it ?
> Thanks
>
|||http://www.aspfaq.com/2177
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DigitalGus" <papierCrayon@.hotmail.com> wrote in message
news:KFjkc.25427$k%.581253@.news20.bellglobal.com.. .
>I try to identify the value of increment property of a column.
> INFORMATION_SCHEMA does not suppply it.
> Somebody know how to get it ?
> Thanks
>
sql

INFORMATION_SCHEMA and increment

I try to identify the value of increment property of a column.
INFORMATION_SCHEMA does not suppply it.
Somebody know how to get it ?
ThanksThe ANSI standard INFORMATION_SCHEMA views don't expose proprietary
extensions like IDENTITY values. However, you can augment the results using
functions like IDENT_CURRENT. For example:
USE Northwind
SELECT IDENT_CURRENT(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
) AS CurrentIdentity
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'Orders'
Hope this helps.
Dan Guzman
SQL Server MVP
"DigitalGus" <papierCrayon@.hotmail.com> wrote in message
news:KFjkc.25427$k%.581253@.news20.bellglobal.com...
> I try to identify the value of increment property of a column.
> INFORMATION_SCHEMA does not suppply it.
> Somebody know how to get it ?
> Thanks
>|||http://www.aspfaq.com/2177
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DigitalGus" <papierCrayon@.hotmail.com> wrote in message
news:KFjkc.25427$k%.581253@.news20.bellglobal.com...
>I try to identify the value of increment property of a column.
> INFORMATION_SCHEMA does not suppply it.
> Somebody know how to get it ?
> Thanks
>

INFORMATION_SCHEMA and increment

I try to identify the value of increment property of a column.
INFORMATION_SCHEMA does not suppply it.
Somebody know how to get it ?
ThanksThe ANSI standard INFORMATION_SCHEMA views don't expose proprietary
extensions like IDENTITY values. However, you can augment the results using
functions like IDENT_CURRENT. For example:
USE Northwind
SELECT IDENT_CURRENT(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
) AS CurrentIdentity
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'Orders'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DigitalGus" <papierCrayon@.hotmail.com> wrote in message
news:KFjkc.25427$k%.581253@.news20.bellglobal.com...
> I try to identify the value of increment property of a column.
> INFORMATION_SCHEMA does not suppply it.
> Somebody know how to get it ?
> Thanks
>|||http://www.aspfaq.com/2177
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DigitalGus" <papierCrayon@.hotmail.com> wrote in message
news:KFjkc.25427$k%.581253@.news20.bellglobal.com...
>I try to identify the value of increment property of a column.
> INFORMATION_SCHEMA does not suppply it.
> Somebody know how to get it ?
> Thanks
>

INFORMATION_SCHEMA again

Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
use tempdb
go
if object_id('temp_proc') is not null drop proc temp_proc
go
create proc temp_proc
as
SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
go
exec tempdb.dbo.temp_proc
use master
exec tempdb.dbo.temp_procThe procedure is still executed in the context of tempdb, not of master. I
think it would be very unexpected behavior if you changed information_schema
views to real user tables, and calling pubs.dbo.someprocedure from tempdb,
tried to find an authors table in tempdb?
A
"Jay" <nospan@.nospam.org> wrote in message
news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
> use tempdb
> go
> if object_id('temp_proc') is not null drop proc temp_proc
> go
> create proc temp_proc
> as
> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
> FROM INFORMATION_SCHEMA.TABLES
> go
> exec tempdb.dbo.temp_proc
> use master
> exec tempdb.dbo.temp_proc
>|||Huh?
Are you saying that a procedure will always execute within the database it
was created in?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
> The procedure is still executed in the context of tempdb, not of master.
> I think it would be very unexpected behavior if you changed
> information_schema views to real user tables, and calling
> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
> tempdb?
> A
>
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>|||Would something else make more sense?
USE Pubs;
GO
CREATE PROCEDURE dbo.GetAuthors
AS
SELECT * FROM Authors;
GO
USE master;
GO
EXEC Pubs.dbo.GetAuthors;
You expect this to return rows from master.dbo.Authors?
"Jay" <nospan@.nospam.org> wrote in message
news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
> Huh?
> Are you saying that a procedure will always execute within the database it
> was created in?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>|||I expected to be able to write a general purpose procedure that would detect
the catalog of the database it was executed in. In the example you gave, I
would expect it to complain that there was no Authors table, not go back to
the database the procedure was created in and read the Authors table in
there while the specified database context was master.
However, looking back at the nightly maintenance procedure I wrote, do do
this I have to get cute with dynamic sql and embeded 'use' statemants.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23NBYCrCIHA.4472@.TK2MSFTNGP05.phx.gbl...
> Would something else make more sense?
>
> USE Pubs;
> GO
> CREATE PROCEDURE dbo.GetAuthors
> AS
> SELECT * FROM Authors;
> GO
> USE master;
> GO
> EXEC Pubs.dbo.GetAuthors;
>
> You expect this to return rows from master.dbo.Authors?
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
>> Huh?
>> Are you saying that a procedure will always execute within the database
>> it was created in?
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>>
>

Information_schema ?

Hello,
When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).
ThanksThese are system views set up in the master database.

They can be used as templates for other databases to get meta data info. Like table, constraint and view info.

All you do is copy them accross into query anyalyzer and create them on your user database.

Cheers

Information_Schema

Hi,
Is there a way to get table Creation Date
using Information_Schema?
Thanks,
RogerNo.
SELECT crdate FROM sysobjects
David Portas
SQL Server MVP
--|||One caveat to be aware of is that crdate is not updated when a stored
procedure is changed using the ALTER PROCEDURE statement. It truly reflects
when the procedure/object was first created, not when it was last changed or
compiled.
--Brian
(Please reply to the newsgroups only.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124218635.981530.150360@.o13g2000cwo.googlegroups.com...
> No.
> SELECT crdate FROM sysobjects
> --
> David Portas
> SQL Server MVP
> --
>sql

Information/ErrorCode from FireInformation/Error

Hi,
Very low priority this one...

Is there a "free" number range that we should use for the InformationCode/ErrorCode parameter to the FireInformation/FireError methods? Currently I just use 1 and -1 respectively.

On a similar note...I remember someone from MS (might have been Matt) once saying that a list of all the rror codes would be published at some point. Is this still the plan and if so, where will it be?

Thanks
JamieJamie,

I can respond only to the 2nd half of your question, on documentation.

As all of the errors are fields in the Microsoft.SqlServer.Dts.Runtime.HResults class, they are already listed in the API reference, particularly in the HResults Members topic in BOL at ms-help://sql90/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_HResults_Members.htm.

Unfortunately this and similar topics are auto-generated by our doc tools in a format that we can't modify. Therefore this particular list contains the "symbolic name" for each error (eg DTS_E_64BITVARIABLERECAST) and its description...but NOT the actual numeric HRESULT.

For the Web refresh of BOL that we're releasing around RTM, we've added a another similar topic that DOES contain the actual HRESULT codes (in hex, as you'll see them in real life), titled "Integration Services Error Reference."

Hope this helps,

-Doug|||Doug,
Frans Van Bree had a good question here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2254.aspx?CommentPosted=true#commentmessage

Will all the event codes from the OnInformation event also be in this reference?

-Jamie|||It's my understanding that errors, warnings, and informational messages from Microsoft-provided Integration Services components are all included in the HResults class already documented (though without the hex codes) in the API reference, as mentioned in my previous post.

These will also be included, with hexadecimal codes, in the new topic that I mentioned. Its planned title, "Error Reference," may be somewhat misleading, but there could be youngsters out there for whom "hresult" is meaningless. <g>

In other words, the new list, like the existing one, will include not only the messages whose symbolic names begin DTS_E_*, but also DTS_I_*, DTS_MSG_*, etc.

(Off topic: My favorite, to date, is DTS_I_CANTRELIEVEPRESSURE.)

-Doug

Information Wont Post To My Database

I'm having a weird problem with an easy process. I have a section that allows people to enter their name and email address if they wish to be contacted. I have it set up so that when they enter that information into the text boxes, the info is then sent to my SQL database. The code looks right to me, but it never comes up. Here is the code I have for the button_click.

Dim

NTAdatasourceAsNew SqlDataSource()

NTAdatasource.ConnectionString = ConfigurationManager.ConnectionStrings(

"DatabaseConnectionString1").ToString()

NTAdatasource.InsertCommandType = SqlDataSourceCommandType.Text

NTAdatasource.InsertCommand =

"INSERT into ContactUs (YourName, EmailAddress, DateTimeStamp) VALUES (@.YourName, @.EmailAddress, @.DateTimeStamp)"

NTAdatasource.InsertParameters.Add(

"YourName", txtYourName.Text)

NTAdatasource.InsertParameters.Add(

"EmailAddress", txtEmailAddress.Text)

NTAdatasource.InsertParameters.Add(

"DateTimeStamp", DateTime.Now)Dim RowsAddedAsInteger = 0Try

RowsAdded = NTAdatabase.Insert()

Catch exAs Exception

Server.Transfer(

"problem.aspx")EndTry

If RowsAdded <> 1Then

Server.Transfer(

"problem.aspx")Else

Server.Transfer(

"success.aspx")EndIf

EndSub

The strange thing is that when I debug, the "rowsadded" value comes up to 1. The process runs through debugging just fine and redirects me to my "success.aspx" page. What am I doing wrong?

Thanks

Never mind...figured it out.