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
>