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 Minimum day in a continuous series

Hi,

l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?

Date
--
20/5
19/5
18/5
17/5
16/5
15/5
14/5
09/5
07/5
06/5
05/5
And what about May/5 May/6 and May/7?.. those also meet your request.|||

The only thing that comes to mind is to use a cursor to loop through your rows.

You can then check each row to determine if it is exactly one day beyond the last row.

Do you just want to get a count of the highest number of days? Or what do you want to return?

|||

If you are using SQL Server 2005 you
can do this (this can be modified to work
with SQL Server 2000 if required)

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006');

with dt_rn(dt,rn)
as
(select dt,
dt-rank() over(order by dt)
from #dates)
select convert(char(5),min(dt),103) as EventStart,
count(*) as ContinuousDays
from dt_rn
group by rn
having count(*)>1

drop table #dates

|||

Hello

To get the Minimum day of each sequence you could try this...

drop table #dates

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006')

select * from #dates od where not exists (select dt from #dates id where id.dt = od.dt-1)
and exists (select dt from #dates id2 where id2.dt = od.dt+1)

The 2nd Part of the where clause is needed to eliminate the 09/05 date... If you consider that single date a "sequence" also then remove the 2nd part.

Be warned though that this querry can eat up a lot of performance.

|||this query must return the event start date and count of continous event dates. you must to replace date_column and table_name

select start_date, count(event_date) as cnt from (
select date_column as event_date,
(select max(date_column) from (
select date_column
from table_name t1
left outer join table_name t2
on t1.date_column=dateadd(t2,1,date_column)
where t2.date is null
) as start_dates
where start_dates.date_column<=events.date_column
) as start_date
from table_name events
) as event_dates
group by event_start_date