List Tables Associated with Specific Partition Scheme

How do I list the tables associated with a particular partition scheme? Basically I need to join between sys.tables and sys.data_spaces. The sys.tables view has a field for lob_data_space_id, but this seems to apply to the large object fields.

-Darrell

select ps.name as SchemaName, obj.name as TableName from sys.partition_schemes ps

join sys.indexes idx on (ps.data_space_id=idx.data_space_id)

join sys.objects obj on (idx.object_id=obj.object_id)

|||

This tells me which indexes are partitioned on the partition scheme, but not exactly the tables. This only works for tables if you assume that each table has at least one index created on the same partition scheme as the table itself. I want to be able to deal with the scenario where the table may not have any indexes create on the same partition as the base table.

The partitioned table may have no indexes or it may have indexes that are partitioned on a different scheme.

-Darrell

List Tables Associated with Specific Partition Scheme

How do I list the tables associated with a particular partition scheme? Basically I need to join between sys.tables and sys.data_spaces. The sys.tables view has a field for lob_data_space_id, but this seems to apply to the large object fields.

-Darrell

select ps.name as SchemaName, obj.name as TableName from sys.partition_schemes ps

join sys.indexes idx on (ps.data_space_id=idx.data_space_id)

join sys.objects obj on (idx.object_id=obj.object_id)

|||

This tells me which indexes are partitioned on the partition scheme, but not exactly the tables. This only works for tables if you assume that each table has at least one index created on the same partition scheme as the table itself. I want to be able to deal with the scenario where the table may not have any indexes create on the same partition as the base table.

The partitioned table may have no indexes or it may have indexes that are partitioned on a different scheme.

-Darrell

List of SP names and parameters

I need to come up with a list of all Stored Procedures in
a given database and the associated parameters passed,
something like this:
Proc Name parmater
proc1 a,b
proc2 a
proc3 -
I would really appreciate if somesone can shed some light
on how to do this.
Thankswww.aspfaq.com/2463
"Adel Asaad" <Adel.Asaad@.Trade-ranger.com> wrote in message
news:52f201c34195$4880d0c0$a401280a@.phx.gbl...
> I need to come up with a list of all Stored Procedures in
> a given database and the associated parameters passed,
> something like this:
> Proc Name parmater
> proc1 a,b
> proc2 a
> proc3 -
> I would really appreciate if somesone can shed some light
> on how to do this.
> Thanks|||Thank you very much. That did it - I really appreciate
the quick response.
Adel
>--Original Message--
>www.aspfaq.com/2463
>
>
>"Adel Asaad" <Adel.Asaad@.Trade-ranger.com> wrote in
message
>news:52f201c34195$4880d0c0$a401280a@.phx.gbl...
>> I need to come up with a list of all Stored Procedures
in
>> a given database and the associated parameters passed,
>> something like this:
>> Proc Name parmater
>> proc1 a,b
>> proc2 a
>> proc3 -
>> I would really appreciate if somesone can shed some
light
>> on how to do this.
>> Thanks
>
>.
>

List database users & login

In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peter
Hello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>

List database users & login

In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>

List database users & login

In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>

List all triggers for a table or an entire database

How can I get a list of all the triggers associated with a particular
table in a database. And in addition, how can I get a list of all
triggers for the entire database (meaning all triggers associated with
all tables)? Thanks!!
Regards,
Troy
Troy,
[vbcol=seagreen]
answered my own question... here's the query in case someone else is
wondering:
SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S1.deltrig
> 0 THEN 'Delete' WHEN S1.instrig > 0 THEN 'Insert' WHEN S1.updtrig > 0
THEN 'Update' END 'TriggerType' FROM sysobjects S1 JOIN sysobjects S2 ON
S1.parent_obj = S2.[id] WHERE S1.xtype='TR'
Regards,
Troy