Get statistics on db objects

I'm using something like:

SELECT COUNT(*) 'Number of System Tables' FROM dbo.sysobjects

WHERE xtype = 's'

to get statistics on database objects but I seem to remember that theres a more efficient way. For example, is there some way to add something like a where clause to the first table returned by sp_help (where Object_type = 'view', for example)?

Thanks,

Dave

SP_TABLES can be used in this csae with parameters for '

Table, system table, or view.'

Get Server\Instance Name

I can't find this anywhere in the thousands of objects in SSIS. So I thought I would ask.

I have a situtation where I want to retrieve the server\instance name of the SSIS job is running on. I have multiple servers and instances and I want to send emails like "Failed see server\instance - job name".

Any ideas?

Thanks

There is a System variable in SSIS called MachineName that, I think, solves half of what you want.

Rafael Salas

|||

Tom Phillips wrote:

I can't find this anywhere in the thousands of objects in SSIS. So I thought I would ask.

I have a situtation where I want to retrieve the server\instance name of the SSIS job is running on. I have multiple servers and instances and I want to send emails like "Failed see server\instance - job name".

Any ideas?

Thanks

A SSIS package does not run on a server/instance. Each execution is a standalone process (you can see it in Task Manager as dtexec.exe). There is no server component required in order to execute packages.

-Jamie

|||That is true. However the job which kicked off the SSIS package did start on a server. That is actually what I am trying to get.

Maybe I will have to pass it in the job to the SSIS package?|||

Tom Phillips wrote:

Maybe I will have to pass it in the job to the SSIS package?

That might be the way to go...

Rafael Salas

|||

Tom Phillips wrote:

That is true. However the job which kicked off the SSIS package did start on a server. That is actually what I am trying to get.

Maybe I will have to pass it in the job to the SSIS package?

OK, so the job is completely different to the package. There is no way for the SSIS package to know where it was executed from unless you explicitly tell it so yes, you will have to pass it in.

-Jamie

get properties of a relation with SQL - DMO

Hi,
I am going to gather information of the database objects and (after a bypass
– stored procedures) use the SQL-DMO.
What I get information of relations (constraints, foreign keys as you like) ?
I found only Chek and Key objects.
How can I get actions on insert and delete event (’no action’ or ’cascade’).
I get key objects (LPSQLDMOKEY) of a table object (LPSQLDMOTABLE) and it
have only functions
GetName(&bstrName);
GetReferencedTable(&bstrRefTable);
GetReferencedKey(&bstrRefKey);
GetKeyColumns(&lpKeyColumns);
GetReferencedColumns(&lpBaseColumns);
I have to know :
base table
referenced table
column in base table (foreign key column)
column in referenced table (primary key column)
action insert
action delete
The first way was stored procedures, I had give it up on column properties
(identity and pk property was not clear) .
Now I am come up to relations (through columns and indexes) and type more
code. :-)
One more way what I know is the OLEDB schemas.
Give me hope to exists a solution and not need to throw away my program! :-)
Thanks for any idea,
Imre
=?Utf-8?B?SW1yZSBBbWVudA==?= <ImreAment@.discussions.microsoft.com> wrote
in news:1DF30CA9-4855-482F-91AD-164E2C6BB621@.microsoft.com:

> Hi,
> I am going to gather information of the database objects and (after a
> bypass – stored procedures) use the SQL-DMO.
> What I get information of relations (constraints, foreign keys as you
> like) ? I found only Chek and Key objects.
> How can I get actions on insert and delete event (’no action’ or
> ’cascade’).
I assume you mean Insert and Delete Triggers. These are to be found in
the Triggers collection of the Table object.

> I get key objects (LPSQLDMOKEY) of a table object
> (LPSQLDMOTABLE) and it have only functions
> GetName(&bstrName);
> GetReferencedTable(&bstrRefTable);
> GetReferencedKey(&bstrRefKey);
> GetKeyColumns(&lpKeyColumns);
> GetReferencedColumns(&lpBaseColumns);
These are "property getters", e.g. GetName gets the Name property of the
object.

> I have to know :
> base table
> referenced table
> column in base table (foreign key column)
> column in referenced table (primary key column)
> action insert
> action delete
> The first way was stored procedures, I had give it up on column
> properties (identity and pk property was not clear) .
> Now I am come up to relations (through columns and indexes) and type
> more code. :-)
> One more way what I know is the OLEDB schemas.
> Give me hope to exists a solution and not need to throw away my
> program! :-)
I recommend that you look at the SQL-DMO object model in the SQL Server
2000 Books Online (which has active links) or
http://msdn2.microsoft.com/en-us/library/ms141100.aspx with no active
links :-(
However, you should note that DMO is deprecated in favour of SMO and may
be removed in some future version. However, SMO is not available in
versions prior to SQL Server 2005.

get properties of a relation with SQL - DMO

Hi,
I am going to gather information of the database objects and (after a bypass
– stored procedures) use the SQL-DMO.
What I get information of relations (constraints, foreign keys as you like)
?
I found only Chek and Key objects.
How can I get actions on insert and delete event (’no action’ or ’casc
ade’).
I get key objects (LPSQLDMOKEY) of a table object (LPSQLDMOTABLE) and it
have only functions
GetName(&bstrName);
GetReferencedTable(&bstrRefTable);
GetReferencedKey(&bstrRefKey);
GetKeyColumns(&lpKeyColumns);
GetReferencedColumns(&lpBaseColumns);
I have to know :
base table
referenced table
column in base table (foreign key column)
column in referenced table (primary key column)
action insert
action delete
The first way was stored procedures, I had give it up on column properties
(identity and pk property was not clear) .
Now I am come up to relations (through columns and indexes) and type more
code. :-)
One more way what I know is the OLEDB schemas.
Give me hope to exists a solution and not need to throw away my program! :-)
Thanks for any idea,
Imreexamnotes <ImreAment@.discussions.microsoft.com> wrote
in news:1DF30CA9-4855-482F-91AD-164E2C6BB621@.microsoft.com:

> Hi,
> I am going to gather information of the database objects and (after a
> bypass – stored procedures) use the SQL-DMO.
> What I get information of relations (constraints, foreign keys as you
> like) ? I found only Chek and Key objects.
> How can I get actions on insert and delete event (’no action’ or
> ’cascade’).
I assume you mean Insert and Delete Triggers. These are to be found in
the Triggers collection of the Table object.

> I get key objects (LPSQLDMOKEY) of a table object
> (LPSQLDMOTABLE) and it have only functions
> GetName(&bstrName);
> GetReferencedTable(&bstrRefTable);
> GetReferencedKey(&bstrRefKey);
> GetKeyColumns(&lpKeyColumns);
> GetReferencedColumns(&lpBaseColumns);
These are "property getters", e.g. GetName gets the Name property of the
object.

> I have to know :
> base table
> referenced table
> column in base table (foreign key column)
> column in referenced table (primary key column)
> action insert
> action delete
> The first way was stored procedures, I had give it up on column
> properties (identity and pk property was not clear) .
> Now I am come up to relations (through columns and indexes) and type
> more code. :-)
> One more way what I know is the OLEDB schemas.
> Give me hope to exists a solution and not need to throw away my
> program! :-)
I recommend that you look at the SQL-DMO object model in the SQL Server
2000 Books Online (which has active links) or
http://msdn2.microsoft.com/en-us/library/ms141100.aspx with no active
links :-(
However, you should note that DMO is deprecated in favour of SMO and may
be removed in some future version. However, SMO is not available in
versions prior to SQL Server 2005.

get properties of a relation with SQL - DMO

Hi,
I am going to gather information of the database objects and (after a bypass
â' stored procedures) use the SQL-DMO.
What I get information of relations (constraints, foreign keys as you like) ?
I found only Chek and Key objects.
How can I get actions on insert and delete event (â'no actionâ' or â'cascadeâ').
I get key objects (LPSQLDMOKEY) of a table object (LPSQLDMOTABLE) and it
have only functions
GetName(&bstrName);
GetReferencedTable(&bstrRefTable);
GetReferencedKey(&bstrRefKey);
GetKeyColumns(&lpKeyColumns);
GetReferencedColumns(&lpBaseColumns);
I have to know :
base table
referenced table
column in base table (foreign key column)
column in referenced table (primary key column)
action insert
action delete
The first way was stored procedures, I had give it up on column properties
(identity and pk property was not clear) .
Now I am come up to relations (through columns and indexes) and type more
code. :-)
One more way what I know is the OLEDB schemas.
Give me hope to exists a solution and not need to throw away my program! :-)
Thanks for any idea,
Imre=?Utf-8?B?SW1yZSBBbWVudA==?= <ImreAment@.discussions.microsoft.com> wrote
in news:1DF30CA9-4855-482F-91AD-164E2C6BB621@.microsoft.com:
> Hi,
> I am going to gather information of the database objects and (after a
> bypass â' stored procedures) use the SQL-DMO.
> What I get information of relations (constraints, foreign keys as you
> like) ? I found only Chek and Key objects.
> How can I get actions on insert and delete event (â'no actionâ' or
> â'cascadeâ').
I assume you mean Insert and Delete Triggers. These are to be found in
the Triggers collection of the Table object.
> I get key objects (LPSQLDMOKEY) of a table object
> (LPSQLDMOTABLE) and it have only functions
> GetName(&bstrName);
> GetReferencedTable(&bstrRefTable);
> GetReferencedKey(&bstrRefKey);
> GetKeyColumns(&lpKeyColumns);
> GetReferencedColumns(&lpBaseColumns);
These are "property getters", e.g. GetName gets the Name property of the
object.
> I have to know :
> base table
> referenced table
> column in base table (foreign key column)
> column in referenced table (primary key column)
> action insert
> action delete
> The first way was stored procedures, I had give it up on column
> properties (identity and pk property was not clear) .
> Now I am come up to relations (through columns and indexes) and type
> more code. :-)
> One more way what I know is the OLEDB schemas.
> Give me hope to exists a solution and not need to throw away my
> program! :-)
I recommend that you look at the SQL-DMO object model in the SQL Server
2000 Books Online (which has active links) or
http://msdn2.microsoft.com/en-us/library/ms141100.aspx with no active
links :-(
However, you should note that DMO is deprecated in favour of SMO and may
be removed in some future version. However, SMO is not available in
versions prior to SQL Server 2005.