Listing tables in a database

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?
> RichHello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> 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,
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:
[vbcol=seagreen]
> 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:
>|||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:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||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'|||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:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||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 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...
>
>