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 tables without indexes

Using SS2000 SP4. I found this code:
USE SMCLMS_Dev;
GO
SELECT*
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY table_name;
GO
but when I run it I get "Invalid object name 'sys.tables'."
Thanks,
--
Dan D.That example uses the sys.tables catalog view and is only valid for SQL
Server 2005. For an equivalent example in SQL Server 2000, try this:
SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
ORDER BY name;
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
> Using SS2000 SP4. I found this code:
> USE SMCLMS_Dev;
> GO
> SELECT*
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
> ORDER BY table_name;
> GO
> but when I run it I get "Invalid object name 'sys.tables'."
> Thanks,
> --
> Dan D.|||That worked. Thanks Gail.
--
Dan D.
"Gail Erickson [MS]" wrote:
> That example uses the sys.tables catalog view and is only valid for SQL
> Server 2005. For an equivalent example in SQL Server 2000, try this:
> SELECT *
> FROM sysobjects
> WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
> ORDER BY name;
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
> > Using SS2000 SP4. I found this code:
> >
> > USE SMCLMS_Dev;
> > GO
> > SELECT*
> > FROM sys.tables
> > WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
> > ORDER BY table_name;
> > GO
> >
> > but when I run it I get "Invalid object name 'sys.tables'."
> >
> > Thanks,
> > --
> > Dan D.
>
>

list of tables without indexes

Using SS2000 SP4. I found this code:
USE SMCLMS_Dev;
GO
SELECT*
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY table_name;
GO
but when I run it I get "Invalid object name 'sys.tables'."
Thanks,
--
Dan D.That example uses the sys.tables catalog view and is only valid for SQL
Server 2005. For an equivalent example in SQL Server 2000, try this:
SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
ORDER BY name;
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
> Using SS2000 SP4. I found this code:
> USE SMCLMS_Dev;
> GO
> SELECT*
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
> ORDER BY table_name;
> GO
> but when I run it I get "Invalid object name 'sys.tables'."
> Thanks,
> --
> Dan D.|||That worked. Thanks Gail.
--
Dan D.
"Gail Erickson [MS]" wrote:

> That example uses the sys.tables catalog view and is only valid for SQL
> Server 2005. For an equivalent example in SQL Server 2000, try this:
> SELECT *
> FROM sysobjects
> WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
> ORDER BY name;
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
>
>

List of all database users

We had 40+ databases and like to see all of users in these databases. What
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:

> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
>
>

List of all database users

We had 40+ databases and like to see all of users in these databases. What
is the data dictionary, the system table, or the sys view can list all
database users?Hello,
Execute the below system procedure with no parameters.
sp_helplogins
Thanks
Hari
"sesciber" <sesciber@.discussions.microsoft.com> wrote in message
news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> We had 40+ databases and like to see all of users in these databases.
> What
> is the data dictionary, the system table, or the sys view can list all
> database users?|||Thanks. sp_helplogins is for a list of login users of sql server and
sp_helpuser is for a list of users for one database only. But I am looking
for how to get all users for all databases that which system table or any
T-SQL script can do.
"Hari Prasad" wrote:
> Hello,
> Execute the below system procedure with no parameters.
> sp_helplogins
> Thanks
> Hari
> "sesciber" <sesciber@.discussions.microsoft.com> wrote in message
> news:46247DD1-EFD4-4FDE-8D0A-75D67F84AF04@.microsoft.com...
> > We had 40+ databases and like to see all of users in these databases.
> > What
> > is the data dictionary, the system table, or the sys view can list all
> > database users?
>
>