getting a list of user created tables ONLY

sql server 2k
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>

Get uniqueness of a column from the system tables or information_schema

I'm trying to write a query which, from a given table name, will
produce a list of column names with an indicator as to whether it is
unique. By unique, I mean it a) is the column in a single-column
primary key, b) is the column in a single-column unique constraint, or
c) is the column in single-column unique index.
So, for this DDL,
-- CODE BEGINS
create table t1 (
c1 int not null primary key,
c2 int not null unique,
c3 int not null,
c4 int not null
)
create unique index ix1 on t1 (c3)
-- drop table t1
-- CODE ENDS
I'd like a query that will produce something like this output
c1 yes
c2 yes
c3 yes
c4 no
I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
information_schema, but I'm getting nowhere. Anyone have any hints?
Thomas BergI forgot to say: I'm using SQL Server 2000 SP4.|||Hello, Thomas
This query returns the desired result:
SELECT name,
CASE WHEN EXISTS (
SELECT * FROM sysindexkeys k
INNER JOIN sysindexes i
ON k.id=i.id AND k.indid=i.indid
WHERE k.id=c.id AND k.colid=c.colid
AND INDEXPROPERTY(i.id,i.name,'IsUnique')=1
AND NOT EXISTS (
SELECT * FROM sysindexkeys k2
WHERE k.id=k2.id AND k.indid=k2.indid
AND k.keyno<>k2.keyno
)
) THEN 'yes' ELSE 'no' END AS IsUnique
FROM syscolumns c WHERE id=OBJECT_ID('t1')
Note that it's sufficient to search only for unique indexes, because
primary keys and unique keys are always enforced by creating a unique
index with the same name on the specified columns.
For a more thorough testing of the query, I added the following:
create unique index ix2 on t1 (c4,c3)
create index ix3 on t1 (c4)
Razvan|||tbergNoSpamPlease@.insight-system.co.jp a crit :
> I'm trying to write a query which, from a given table name, will
> produce a list of column names with an indicator as to whether it is
> unique. By unique, I mean it a) is the column in a single-column
> primary key, b) is the column in a single-column unique constraint, or
> c) is the column in single-column unique index.
> So, for this DDL,
> -- CODE BEGINS
> create table t1 (
> c1 int not null primary key,
> c2 int not null unique,
> c3 int not null,
> c4 int not null
> )
> create unique index ix1 on t1 (c3)
> -- drop table t1
> -- CODE ENDS
> I'd like a query that will produce something like this output
> c1 yes
> c2 yes
> c3 yes
> c4 no
> I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
> information_schema, but I'm getting nowhere. Anyone have any hints?
> Thomas Berg
>
Here is a very general query wich give you all informations about
indexes with columns and uniqueness
SELECT
u.name AS IXD_SCHEMA_NAME,
o.name AS IXD_TABLE_NAME,
i.name AS IXD_INDEX_NAME,
CONSTRAINT_TYPE AS IXD_CONSTRAINT_TYPE,
CASE
WHEN i.indid = 0 THEN 'TABLE'
WHEN i.indid = 1 THEN 'CLUSTER'
WHEN i.indid BETWEEN 2 AND 254 THEN 'HEAP'
WHEN i.indid = 255 THEN 'TXTEIMAGE'
END AS IXD_INDEX_TYPE,
INDEXPROPERTY(o.id, i.name, 'IsUnique') AS IXD_IS_UNIQUE,
INDEXPROPERTY(o.id, i.name, 'IndexFillFactor') AS IXD_FILL_FACTOR,
c.name AS IXD_COL_NAME,
DATA_TYPE + '('+
CAST(COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) AS
VARCHAR(16))
+ COALESCE(', '+CAST(NULLIF(NUMERIC_SCALE, 0) AS
VARCHAR(16)) , '') +')' AS IXD_COL_TYPE,
k.keyno AS IXD_COL_IDX_ORDER,
CASE
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 0 THEN 'ASC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 1 THEN 'DESC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) IS NULL THEN ''
END AS IXD_COL_DATA_ORDER,
INDEXPROPERTY(o.id, i.name, 'IsRowLockDisallowed') AS
IXD_ROW_LOCK_DISALLOWED,
INDEXPROPERTY(o.id, i.name, 'IsPageLockDisallowed') AS
IXD_PAGE_LOCK_DISALLOWED
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o
ON i.id = o.id
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
INNER JOIN dbo.sysindexkeys k
ON o.id = k.id
and i.indid = k.indid
INNER JOIN dbo.syscolumns c
ON k.colid = c.colid
and o.id = c.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON u.name = ISC.TABLE_SCHEMA
AND o.name = ISC.TABLE_NAME
AND c.name = ISC.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
ON u.name = TCT.CONSTRAINT_SCHEMA
AND i.name = TCT.CONSTRAINT_NAME
WHERE i.status & 64 <> 64 -- sauf les index "stat"
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||You guys are brilliant. Thanks.