Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2011-03-09 : 07:43:08
|
| hello everybody, i have got a weird requirement,could anybody help me. i have about 300 tables in my database. i have column 'CREATED_BY' and 'CREATED_DATE' in every table. now the requirement is i need to find the tables which have the value as 100 for 'CREATED_BY' and '2011-01-24 00:00:00.000' for 'CREATED_DATE'.Thanx in advance. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-09 : 07:54:02
|
| declare @sql varchar(max)select @sql=isnull(@sql,'') + replace('if exists(select * from [ ! ] where Created_BY = 100 and Created_date = ''20110124'' ) INSERT INTO someTable Values( '' ! '') ',' ! ', table_name) from INFORMATION_SCHEMA.TABLES c where table_type = 'BASE TABLE' JimEveryday I learn something that somebody else already knew |
 |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2011-03-09 : 08:35:04
|
| Iam unable to understand the script..could u please tell me do i need to change anything else in the script.when i execute the script it shows thisThe name 'if exists(select * from [INV_STOCK_TRANSFER_REQUEST_DETAIL] where Created_BY = 100 and Created_date = '20110124' ) INSERT INTO someTable Values( 'INV_STOCK_TRANSFER_REQUEST_DETAIL') if exists(select * from [INV_MATERIAL_STOCK] where Created_BY = 100 and Created_date = '20110124' ) INSERT INTO someTable Values( 'INV_MATERIAL_STOCK') if exists(select * from [RD_SIGNATORIES] where Created_BY = 100 and Created_date = '20110124' ) INSERT INTO someTable Values( 'RD_SIGNATORIES') if exists(select * from [OT_THEATRE_SURGICAL_DISCIPLINE_MAPPING] where Created_BY = 100 and Created_date = '20110124' ) INSERT INTO someTable Values( 'OT_THEA' is not a valid identifier. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-09 : 09:09:24
|
| [code]declare @sql varchar(max)select @sql=isnull(@sql,'') + replace('if exists(select * from [ ! ] where Created_BY = 100 and Created_date = ''20110124'' ) PRINT '' ! '' ',' ! ', table_name) +char(10) + char(13)from INFORMATION_SCHEMA.TABLES c where table_type = 'BASE TABLE' PRINT ( @SQL)[/code]This will make it easier to read. If everything looks okay, change the PRINT @SQL to EXEC(@sql) or just copy the results and run it. Note: I removed the INSERT INTO statement and replaced it with print, as I don't really know your needs.JimYou may want to go thru tools\options\query results\Results To Text and expand how many characters are displayed in the results window.Everyday I learn something that somebody else already knew |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Find the Table