SQL 2K
I ran the below sql to list the table name and column_name with column_name
like 'ID'
select substring(so.name,1,50) 'Table Name',
substring(sc.name,1,50) 'Field Name'
from sysobjects so
inner join syscolumns sc on
so.id = sc.id
where so.type = 'U' and
sc.name like '%ID%'
order by so.name,sc.name
I want to include the rowcount for each table it returns with column ID =
100
Thanks In Advance
SmithUse information schema views instead.
select
table_name,
column_name
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
AMB
select table_name
"MS User" wrote:
> SQL 2K
> I ran the below sql to list the table name and column_name with column_nam
e
> like 'ID'
> select substring(so.name,1,50) 'Table Name',
> substring(sc.name,1,50) 'Field Name'
> from sysobjects so
> inner join syscolumns sc on
> so.id = sc.id
> where so.type = 'U' and
> sc.name like '%ID%'
> order by so.name,sc.name
>
> I want to include the rowcount for each table it returns with column ID =
> 100
> Thanks In Advance
> Smith
>
>|||Thanks Mesa
I will change to use information schema, but then how to get the rowcount
for ID = 100 in all tables it returns
Thanks
Smith
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1907D283-6CEB-446A-9C51-CEEDC1967A69@.microsoft.com...[vbcol=seagreen]
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
>|||Sorry, I did not read the message until the end. You have to use dynamic sql
in order to do this.
Example:
use northwind
go
create table #t (
tname sysname,
cname sysname,
rcnt int
)
declare @.tn sysname
declare @.cn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name),
quotename(column_name)
from
information_schema.columns
where
column_name like '%id%'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
order by
table_name,
ordinal_position;
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
@.tn + N' where ' + @.cn + N' = ''100'''
print @.sql
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t
drop table #t
go
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use information schema views instead.
>
> select
> table_name,
> column_name
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
>
> AMB
>
> select table_name
> "MS User" wrote:
>|||Thanks Mesa, much appreciated for your time.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:72739680-A768-4374-B83E-C49657C2DA1A@.microsoft.com...[vbcol=seagreen]
> Sorry, I did not read the message until the end. You have to use dynamic
> sql
> in order to do this.
> Example:
> use northwind
> go
> create table #t (
> tname sysname,
> cname sysname,
> rcnt int
> )
> declare @.tn sysname
> declare @.cn sysname
> declare @.sql nvarchar(4000)
> declare my_cursor cursor local fast_forward
> for
> select
> quotename(table_schema) + '.' + quotename(table_name),
> quotename(column_name)
> from
> information_schema.columns
> where
> column_name like '%id%'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> order by
> table_name,
> ordinal_position;
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.tn, @.cn
> if @.@.error != 0 or @.@.fetch_status != 0 break
> set @.sql = N'select ''' + @.tn + N''', ''' + @.cn + N''', count(*) from ' +
> @.tn + N' where ' + @.cn + N' = ''100'''
> print @.sql
> insert into #t
> exec sp_executesql @.sql
> end
> close my_cursor
> deallocate my_cursor
> select * from #t
> drop table #t
> go
>
> AMB
>
>
> "Alejandro Mesa" wrote:
>
