quote:

---

Originally posted by RajJol


0Hi,

I have a database called 'Company' that holds around 100 tables. Each of these tables have a number of different columns with a number of different rows.

I need to be able to check all tables within the database that holds a column called 'Date' (should only be in about 75 of these tables). Then I need to check, in each table, that the date matches today's date (2010-09-24).

Can someone help me with this please??

Any help will be much appreciated.

Thank You.

---

quote:

---

Originally posted by rohitvishwakarma

use these two(apply join between the tables on object_id)
SELECT * FROM sys.all_objects
SELECT * FROM sys.all_columns

quote:

---

Originally posted by RajJol


0Hi,

I have a database called 'Company' that holds around 100 tables. Each of these tables have a number of different columns with a number of different rows.

I need to be able to check all tables within the database that holds a column called 'Date' (should only be in about 75 of these tables). Then I need to check, in each table, that the date matches today's date (2010-09-24).

Can someone help me with this please??

Any help will be much appreciated.

Thank You.

---

---

CREATE TABLE #temp
(
id INT IDENTITY(1,1),
tableName VARCHAR(150),
columnName VARCHAR(150),
contains_todays_date bit
)

INSERT INTO #temp (tableName , columnName , contains_todays_date)
SELECT O.name AS tableName,C.name AS columnName,0 
FROM sys.all_objects O 
INNER JOIN sys.all_columns C 
        ON O.object_id =C.object_id
WHERE C.name like '%date%'

--SELECT * FROM #temp
DECLARE @count INT
DECLARE @count_max INT

SET @count = 1 
SELECT @count_max = MAX(id) FROM  #temp

WHILE(@count < @count_max)
BEGIN
      DECLARE @table_name VARCHAR(150)
      DECLARE @column_name VARCHAR(150)
      DECLARE @sql VARCHAR(1000)
            
      SELECT @table_name =  tableName, @column_name = columnName FROM  #temp WHERE id = @count
     
       SET @sql = 'IF EXISTS(SELECT 1 FROM  '
       SET @sql =  @sql + @table_name + ' WHERE ' + @column_name + ' = ' +''''+ CAST(GETDATE()  AS VARCHAR(20)) + ''')'
       SET @sql = @sql + ' BEGIN ' 
       SET @sql = @sql + ' UPDATE  #temp ' 
       SET @sql = @sql + ' SET  contains_todays_date = 1 ' 
       SET @sql = @sql + ' WHERE id = ' + CAST(@count AS VARCHAR(100))
       SET @sql = @sql + ' END ' 
       --PRINT @sql
       EXEC(@sql)

      SET @count = @count + 1
END


SELECT * FROM #temp

quote:

---

Originally posted by madhivanan

You can make use of this
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

Madhivanan

Failing to plan is Planning to fail

---