Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
22864 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
22864 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  
 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.03 seconds. Powered By: Snitz Forums 2000