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'

Listing all Indexes

Still using SQL7.

I am wondering how come there is not an Information_Schema view that
lists indexes? Information_Schema is supposed to be the safest way to
obtain information on metadata, but it appears that the only way to
get a list of indexes is with a system stored proc.Zack Sessions (zcsessions@.visionair.com) writes:
> Still using SQL7.
> I am wondering how come there is not an Information_Schema view that
> lists indexes? Information_Schema is supposed to be the safest way to
> obtain information on metadata, but it appears that the only way to
> get a list of indexes is with a system stored proc.

I guess this is because INFORMATION_SCHEMA comes from ANSI, and ANSI
does not know what an index is, as index is regarded to be an implementation
issue. And someone leaped to the conclusion that INFORMATION_SCHEMA
could replace system tables.

One of the reasons I prefer the system tables (together with property
functions) is that they represent complete information set.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

list schemas of a sql server

hello,

what is the sql server 6.5 equivalent for:

"select catalog_name from information_schema.schemata"

i would like to see a list of available schema's on a server
and this seems to work on 7.0 and newer. would i be able to do
it with the systables ? i have looked through the content of the
systables but i can's see a 'schema' column in any of them.

any help is appreciated.
thanks,
tomINFORMATION_SCHEMA.SCHEMATA shows all databases, not all tables. In
fact, it looks like the documentation is wrong here - according to BOL
2000, the view should list each database "that has permissions for the
current user", however in reality it returns all databases, whether or
not the user can access them. So in SQL 6.5 you can just do this:

select name
from master.dbo.sysdatabases

In MSSQL 2000, you could use HAS_DBACCESS() to show only the DBs which
the user can access, but I don't think there's an equivalent in SQL
6.5.

Simon|||thanks for that simon, it did the trick !!

List record counts of all tables?

Hi All,
Is there a fancy way to list all table names with record counts?
Using table: INFORMATION_SCHEMA.TABLES
Also, Is there a way to initialize/empty all data from all tables?
Thank you very muchUse TRUNCATE to clear out a table

as for space

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename

SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO|||I didn't mean that fancy! It worked nonetheless.

Thanks a million

List only the visible columns in a view?

In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get a
row for every column in a view including those that might not be visible but
used only in the WHERE or ORDER BY clauses for example.
Is there an easy way to reduce the list to only show the columns that
actually appear in the SELECT clause of the view definition?
I've tried examining Profiler when expanding the columns node under the View
in Management Studio as it seems to load only one node per visible column
but haven't been able to make much sense of how it does this.
BTW I'm using that view as it promises to only show views and columns "to
which the current user has permissions". Is this reliable or is there a
preferred way of listing visible views, talbe, & columns etc?
cheers,
Paul.Hi, Paul
Look in the INFORMATION_SCHEMA.COLUMNS view.
Razvan|||Use the catalog schema views...
SELECT
name
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.YourViewName');
"Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get
> a
> row for every column in a view including those that might not be visible
> but
> used only in the WHERE or ORDER BY clauses for example.
> Is there an easy way to reduce the list to only show the columns that
> actually appear in the SELECT clause of the view definition?
> I've tried examining Profiler when expanding the columns node under the
> View
> in Management Studio as it seems to load only one node per visible column
> but haven't been able to make much sense of how it does this.
> BTW I'm using that view as it promises to only show views and columns "to
> which the current user has permissions". Is this reliable or is there a
> preferred way of listing visible views, talbe, & columns etc?
> cheers,
> Paul.
>|||Thanks Aaron - much appreciated.
Is there an easy way to determine which columns a user might have access to
when using the "sys" tables? That was what the INFORMATION_SCHEMA views
seemed to promise although there was some doubt in newsgroups as to their
accuracy in SQL2000 due to them being derived from the sysdepends table.
I'm betting that this will have changed in 2005.
However moving back to the sys tables/views will mean I have to determine
this permission information in some other way. Any seggestions would be
appreciated tremendously.
cheers,
Paul.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCq14hWBGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Use the catalog schema views...
> SELECT
> name
> FROM
> sys.columns
> WHERE
> object_id = OBJECT_ID('dbo.YourViewName');
>
> "Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
> message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
>|||> Is there an easy way to determine which columns a user might have access
> to when using the "sys" tables?
Take a look at sys.database_permissions
I believe the join between sys.database_permissions p and sys.comments c
would be:
ON
c.object_id = p.major_id
AND c.column_id = p.minor_id
WHERE
p.class = 1
AND p.grantee_principal_id = SUSER_ID('username');
I am not 100% sure on that, and don't have time today to experiment with
column-level permissions. But hopefully that gets you started.

> accuracy in SQL2000 due to them being derived from the sysdepends table.
Permissions derived from sysdepends? I think that is inaccurate.
A