Listing tables in a database

Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'

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 table names in a database having a particular column.

Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?

Thanks

Check INFORMATION_SCHEMA.COLUMNS view in your BOL.

AMB

|||Thanks!

List Page OverRun

Hi

Is it possible to set a list control not to grow? I have multiple tables in a list box and text boxes it is grouped by a particular column it possible to say that if it the list has to steach to the next page because to much information is to display just don’t go over to the next page just hide it or don’t display the rest of the records

No, list boxes will always grow to accomodate their contents. If an instance of a list does not fit on one page, it will be pushed to the next page. Textboxes and images are the only control that will contrain their contents.|||

Hi Brian,

Thank you for replying, so my if i have to run over to the next page for the same employee so if i am using a lot of ReportItems to have print DataSet Data in the page header and footer for eache page the data changes per employee so is it posible to make the report items repete the same data to the next page if the page is for the same employee so i can have the information for the page header and fotter for that page as well?

Thanks

List of Database Users

In certain applications, it would be useful to be able to display to an
'ordinary' user, a list of other accessors of a particular database. Any
ideas how this can be done?
--
John AustinJohn
Lookup sysusers system table in the BOL
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:1B2F5FAB-E1E6-46DE-9A63-2F00234DD581@.microsoft.com...
> In certain applications, it would be useful to be able to display to an
> 'ordinary' user, a list of other accessors of a particular database. Any
> ideas how this can be done?
> --
> John Austin|||To add on to Uri's response, other methods include executing sp_helpuser or
using DMO.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:1B2F5FAB-E1E6-46DE-9A63-2F00234DD581@.microsoft.com...
> In certain applications, it would be useful to be able to display to an
> 'ordinary' user, a list of other accessors of a particular database. Any
> ideas how this can be done?
> --
> John Austin|||sysusers and sp_helpuser seem to show all the users set up for the database
-
I really want to know about those with an open connection such as can be see
n
in Enterprise Manager in 'Management', 'Current Activity', 'Process Info'
Thanks
--
John Austin
"Dan Guzman" wrote:

> To add on to Uri's response, other methods include executing sp_helpuser o
r
> using DMO.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Austin" <John.Austin@.nospam.nospam> wrote in message
> news:1B2F5FAB-E1E6-46DE-9A63-2F00234DD581@.microsoft.com...
>
>|||sp_who, for instance
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:D8EE51EF-3662-4ED7-A311-F6A26EE6F08B@.microsoft.com...
> sysusers and sp_helpuser seem to show all the users set up for the databas
e -
> I really want to know about those with an open connection such as can be s
een
> in Enterprise Manager in 'Management', 'Current Activity', 'Process Info'
> Thanks
> --
> John Austin
>
> "Dan Guzman" wrote:
>

List of Bugs in Sql 2000

Hi,
Is there any particular site which gives us the list of all bugs in sql 2k?
So that we can avoid those bugs and design the application accordingly.
SanHi,
There is no known bugs in SQL server 2000 which will create issue to your
application.
Have a look into the below link to have an idea on solved bugs.
http://support.microsoft.com/defaul...kb;EN-US;306908
Thanks
Hari
MCDBA
"San" <anonymous@.discussions.microsoft.com> wrote in message
news:F7B61338-70C7-4CBE-9913-41A7131DFC14@.microsoft.com...
> Hi,
> Is there any particular site which gives us the list of all bugs in sql
2k?
> So that we can avoid those bugs and design the application accordingly.
> San|||Search the Microsoft Knowledge Base for SQL and bug, and for articles that
do not contain the word Fix
Jacco Schalkwijk
SQL Server MVP
"San" <anonymous@.discussions.microsoft.com> wrote in message
news:F7B61338-70C7-4CBE-9913-41A7131DFC14@.microsoft.com...
> Hi,
> Is there any particular site which gives us the list of all bugs in sql
2k?
> So that we can avoid those bugs and design the application accordingly.
> San|||> There is no known bugs in SQL server 2000 which will create issue to your
> application.
That's a bold statement!
Check out the Knowledge Base for known bugs.
http://support.microsoft.com/defaul...h;en-gb;kbhowto
Install SP3 to get most of the available fixes.
If you have a specific problem then you can search the KB and the newsgroups
for help but I don't think you should generally have to consider bugs at the
design stage. For most systems I would suggest the known bugs are limited
enough that it should be acceptable to identify and address them during
development and testing.
David Portas
SQL Server MVP
--