Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 17th, 2006, 05:53 AM
Registered User
Join Date: Oct 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem with the INFORMATION_SCHEMA.COLUMNS


i have wrriten this code to find some values in the database but however it doesn't return any values on the
 --set nocount on
              select @res = coalesce( @res + ',' , '') + ' [' + COLUMN_NAME + ']'
                         FROM INFORMATION_SCHEMA.COLUMNS
                         WHERE TABLE_NAME = @columnnameT --AND COLUMN_NAME LIKE @columnnameT2 --AND COLUMN_NAME like '%[^_ID'
              Print @res
              Set @Params1 = N'Select ' + @res + 'from ['+ @tablename1 +'] where ID = ' + @fieldname1 + ''
              Print @res

My full script is...

CREATE TABLE #Results (i int identity, DBName varchar(450), TableName nvarchar(450), ColumnName nvarchar(450))
use master
--set quoted_identifier off
declare @columnnametosearch varchar(128)
declare @Databasetosearch varchar(128)
declare @query varchar(1000)
set @columnnametosearch ='uRALBase'
set @Databasetosearch = 'RALMMA'
set @query='set quoted_identifier off begin use end begin
 select db_name() as databasename,
 object_name(id) as Objectname,
 name as ColumnName from syscolumns
 where name like "'+@columnnametosearch+'%" and db_name() = "'+@Databasetosearch+'" end'
print @query
INSERT INTO #Results exec sp_MSforeachdb @query
SELECT DISTINCT TableName , ColumnName, DBName
FROM #Results

Select Count(*) as [Number of Tables] from #Results

CREATE TABLE #Results1 (i int identity, Value int,BaseID int, Cu_Tbname varchar(450))
declare c cursor for select ColumnName,TableName from #Results
declare @fieldname varchar(450)
declare @tablename varchar(450)
declare @current_tablename varchar(450)
declare @sql nvarchar(500)
declare @Params nvarchar(500)
open c
fetch next from c into @fieldname, @tablename
set @current_tablename = @tablename
while @@FETCH_STATUS = 0
 --set @current_tablename = @tablename
  select @Params=N'@current_tablename nvarchar(100)'
  Set @current_tablename = @tablename
  set @sql = 'select [' + @fieldname + '],ID as [Base Table ID],@current_tablename as [Base Table Name] from [' + @tablename + '] '
  --exec sp_executesql @sql,@Params,@current_tablename
  INSERT INTO #Results1 exec sp_executesql @sql,@Params,@current_tablename --exec(@sql)
  --print @sql
  fetch next from c into @fieldname, @tablename

CREATE TABLE #FechResults (i int identity, Value int,RALBase nvarchar(50), BaseID int, Cu_Tbname varchar(450))
declare @Params1 nvarchar(500)
declare @columnnameT nvarchar(128)
declare @columnnameT2 nvarchar(128)
declare @columnnameT3 nvarchar(128)
declare @current_tablename1 nvarchar(500)
declare @SQLQQ nvarchar(500)
 Select @Params1 = N' SELECT Value AS [Column Value], uRALBase.RALBase AS [RAL Base], BaseID AS [Base Table ID], Cu_Tbname AS [Base Table Name]
                      FROM [#Results1] INNER JOIN
                      uRALBase ON uRALBase.ID = [#Results1].Value
                      WHERE (RALBase IN (''TOS''))'
INSERT INTO #FechResults exec sp_executesql @Params1
Select * from #FechResults
SELECT Count(*) as [Number of Rows]
uRALBase ON uRALBase.ID = [#Results1].Value

declare c1 cursor for select BaseID,Cu_Tbname from #FechResults
declare @fieldname1 nvarchar(450)
declare @tablename1 nvarchar(450)
declare @sqlTWO nvarchar(200)
declare @COLUMN_NAME nvarchar(500)
declare @res nvarchar(4000)
open c1

fetch next from c1 into @fieldname1, @tablename1
while @@FETCH_STATUS = 0
   Set @columnnameT = Rtrim(LTrim('''' + @tablename1 + ''''))
   Set @columnnameT2 = Ltrim(RTRIM('''' + '[' + SUBstring(+@tablename1,2,200)+ ']' + '_' + '%' + ''''))
                       WHERE TABLE_NAME = '+@columnnameT+' AND COLUMN_NAME LIKE '+@columnnameT2+' AND COLUMN_NAME like ''%_ID'''
     --exec sp_executesql @SQLQQ --,@Params1,@columnnameT,@columnnameT2
     --print @SQLQQ

              --set nocount on
              select @res = coalesce( @res + ',' , '') + ' [' + COLUMN_NAME + ']'
                         FROM INFORMATION_SCHEMA.COLUMNS
                         WHERE TABLE_NAME = @columnnameT --AND COLUMN_NAME LIKE @columnnameT2 --AND COLUMN_NAME like '%[^_ID'
              Print @res
              Set @Params1 = N'Select ' + @res + 'from ['+ @tablename1 +'] where ID = ' + @fieldname1 + ''
              Print @res
             -- exec sp_executesql @res
              --exec (@res + 'from ['+ @tablename1 +'] where ID = ' + @fieldname1 + '')
    -- Set @Params1 = N'Select * from ['+ @tablename1 +'] where ID = ' + @fieldname1 + ''
      exec sp_executesql @Params1 --,@Params1,@columnnameT,@columnnameT2
      print @Params1
     fetch next from c1 into @fieldname1, @tablename1

Drop TABLE #Results
Drop TABLE #Results1
Drop TABLE #FechResults
--Select * from uRALBase

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenating Columns in SELECT Problem kevorkian SQL Server 2000 6 June 13th, 2008 02:50 PM
two columns if... gabster XSLT 0 August 15th, 2007 12:21 PM
Problem with sorting asp:tablecell columns rrajeshbe ASP.NET 2.0 Basics 0 June 1st, 2006 08:28 AM
problem adding two columns g_vamsi_krish SQL Server 2000 2 March 6th, 2006 10:56 AM
sub columns preethisg BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 0 December 27th, 2005 01:53 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.