Column Name

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_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
> 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...
> 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:
>> 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
>> Smith
>>|||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:
> 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:
> > 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
> > Smith
> >
> >
> >|||Thanks Mesa, much appreciated for your time.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:72739680-A768-4374-B83E-C49657C2DA1A@.microsoft.com...
> 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:
>> 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:
>> > 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
>> > Smith
>> >
>> >
>> >