Querying table properties - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querying table properties

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-26 : 13:44:30

Aside from looking at a table via Object Explorer, what query functions allow me to view the Created Date?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-26 : 13:51:36

select * from sys.tables
Jim

Everyday I learn something that somebody else already knew

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-26 : 13:57:54

Thanks; more specifically, how would I query a particular table in one database? I'm working on determining statistics for a table in the T database.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-26 : 14:18:06

Using four part name.

Select * from <YourDBName>.<schemaName>.<tableName>

Example:
select * from fundb.sys.tables

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-26 : 14:24:37

Thanks; for example

select * from T.dbo.dtproperties

shows me the fields of the table, but not what the properties of the table are. Via Object Explorer, I see:

ANSI NULLs
Created date
Data space
...
and so on.

I'd love to see these in the result of a query.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-26 : 14:45:51

Unfortunately, not all of that info is in one table. Others that you may want to check out
are sys.indexes,sys.sysindexes
and
[code][/
SELECT

SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)

FROM sys.dm_db_partition_stats
WHERE object_name(object_id) = 'yourTable'

will give you the table size (lifted from the sproc sp_spaceused)

Jim

Everyday I learn something that somebody else already knew

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-26 : 15:14:11

Thanks; the table is not in sys.indexes unfortunately. Also unfortunate is that I do not have access to your second query (I'm not the DBA)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources