SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to loop through each table in a database and c
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RajJol
Starting Member

17 Posts

Posted - 09/24/2010 :  08:40:38  Show Profile  Reply with Quote

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.

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/24/2010 :  08:47:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

rohitvishwakarma
Posting Yak Master

India
232 Posts

Posted - 09/24/2010 :  10:41:19  Show Profile  Reply with Quote
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.


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/28/2010 :  10:29:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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.





OP wants to search for a particular date value too

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rohitvishwakarma
Posting Yak Master

India
232 Posts

Posted - 09/28/2010 :  11:21:09  Show Profile  Reply with Quote
Here is the complete query get the columns having date as column name and whether they contains today's date or not.


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



Thanks
Rohit

Edited by - rohitvishwakarma on 09/28/2010 11:22:51
Go to Top of Page

RajJol
Starting Member

17 Posts

Posted - 10/04/2010 :  06:34:48  Show Profile  Reply with Quote
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



Hi Madhivanan,

I used this link you sent me but am still having a few problems. I modified the code slightly with success but it only works when I specify the table with the date column. If I specify a table which does not have the column it returns an error message. Please see my modified code below


declare @sql varchar(max),@search varchar(100)
set @search='date'
select
@sql=@sql+'select ''Sector'' ''Source'', '''+ t.TABLE_NAME+''' ''TableName'', CONVERT(CHAR(10), InsertDate, 120) ''InsertDate'', DATEDIFF (d,InsertDate, GETDATE()) ''DaysOld'', COUNT(*) ''Count'' FROM'+' ['+t.TABLE_NAME+']'+' GROUP BY CONVERT(CHAR(10), InsertDate, 120), DATEDIFF (d,InsertDate, GETDATE()) union all '
FROM information_schema.tables as t
where
t.table_type='BASE TABLE' and t.TABLE_NAME in ('x_Updates')
--data_type like '%char%'
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000