Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Querying sys tables

Hi Experts,

I'm looking to modify the below query to

A) include a yes/no field, if the table specified has a column named Emp% should be true and false otherwise.
B) add another column, if table specified has column DateEntered and there are records within last 30 days should be true and false otherwise.
SELECT      T.name TableName,i.Rows NumberOfRows
FROM        sys.tables T
JOIN        sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE       indid IN (0,1)
ORDER BY    i.Rows DESC,T.name

Open in new window

Thanks
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image
You might use a totally different approach to get the status regarding DateEntered and make use of sp_msforeachtable

EXEC sp_MSforeachtable 'SELECT ''?'' as TableName, Case When Max(DateEntered) >DATEADD(day,-30, GETDATE()) Then 1 Else 0 End As HasNewData FROM ?';

Open in new window


This will cause error messages for any table without DateEntered column but results for those with that column.

Then you may also look into sys.dm_db_index_usage_stats.or the STATS_DATE() function.

Bye. Olaf.
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image
And if you ask how to get all the results into one table: Define a temp table and execute an INSERT INTO that temp table as the code to execute for each table.

Bye, Olaf.
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

@Olaf,

How can I have that result as part of the original query?
(Similar to what PortletPaul did)
Also the query when not returning an error gives me a record for each table record, while I only need one per table.
How can this be accomplished?

Thanks,
Ben
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image
Well, I already answered the second question. Create a temp table and don't do a SELECT for each table, but an INSERT INTO temptablte SELECT ... and then you collect this data into on result. You can then combine it with further data from sys tales into one final result with more queries.

Bye, Olaf.
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Here you go...

Use a Common Table Expression and some Dynamic SQL 
declare @sql varchar(max) 

;with cte_emp as
( 
-- first up lets get all the tables that have a column like 'Emp%'

   select Distinct schemas.name as [schema]
        ,tables.name as [table]
        ,tables.schema_id 
        ,tables.object_id
--      ,columns.name as [column]
--      ,columns.*, tables.*
  from sys.schemas
  join sys.tables on schemas.schema_id = tables.schema_id
  join sys.columns on tables.object_id = columns.object_id
  where columns.name like 'emp%'
) 
-- Now lets find out if they have a dateenntered or not using another CTE

  , cte_date as
( select distinct c.[schema],c.[table],'Y' as has_emp, case when columns.name is NULL then 'N' else'Y' end as has_date, isnull(columns.name,'') as [date]
  from cte_emp c
  left outer join sys.columns on c.object_id = columns.object_id and columns.name like 'DateEntered'
) 
-- Now we can construct our dynamic SQL to go find the results....

  select @sql = isnull(@sql+char(13)+char(10) +' union all ','') 
              + 'select '''+[schema]+''' as [Schema],'''+[table]+''' as [Table],'''+[date]+''' as [Date],'''+[Has_Emp]+''' as [Has_Emp],'''+[Has_Date]+''' as [Has_Date], 
			    case when '''+Has_Date+''' = ''Y'' then (select top 1 ''Y'' from ['+[schema]+'].['+[table]+'] 
				where '+case when len([date])>1 then '['+[date]+']' else 'getdate()-100' end +' > getdate() - 30) else ''NA'' end as Has_Recent_Dates'  from cte_date  
  
print @sql

exec (@sql)

Open in new window

Let me know if you need explanation...
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi Mark,
When I run this on master Db nothing shows up.
And when I rrun this on desired DB I get the following errors.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "schemas.schema_id" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "tables.schema_id" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "tables.object_id" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "columns.object_id" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "columns.name" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "schemas.name" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "tables.name" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "tables.schema_id" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "tables.object_id" could not be bound.

Open in new window

Thanks,
Ben
Avatar of Mark Wills
Mark Wills
Flag of Australia image
It wont work on Master.

Surprised it didnt work on your target DB.

What version of SQL Server are you running ?

Can you do : 
   select Distinct schemas.name as [schema]
        ,tables.name as [table]
        ,tables.schema_id 
        ,tables.object_id
--      ,columns.name as [column]
--      ,columns.*, tables.*
  from sys.schemas
  join sys.tables on schemas.schema_id = tables.schema_id
  join sys.columns on tables.object_id = columns.object_id
  where columns.name like 'emp%'

Open in new window

Might need to clarify with alias 
   select Distinct s.name as [schema]
        ,t.name as [table]
        ,t.schema_id 
        ,t.object_id
--      ,columns.name as [column]
--      ,columns.*, tables.*
  from sys.schemas s
  join sys.tables t on s.schema_id = t.schema_id
  join sys.columns  c on t.object_id = c.object_id
  where c.name like 'emp%'

Open in new window

Your not running Azure are you ?
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

@Mark,

Your second SQL works.
Now how do I get the column needed (DateEntered yes/no)?
I'm using SQL Server 2008.

Thanks,
Ben
Avatar of Mark Wills
Mark Wills
Flag of Australia image
OK, then please try : 
declare @sql varchar(max) 

;with cte_emp as
( 
-- first up lets get all the tables that have a column like 'Emp%'

   select Distinct s.[name] as [schema]
        ,t.[name] as [table]
        ,t.[schema_id] 
        ,t.[object_id]
  from sys.schemas s
  join sys.tables t on s.[schema_id] = t.[schema_id]
  join sys.columns c on t.[object_id] = c.[object_id]
  where c.[name] like 'emp%'
) 
-- Now lets find out if they have a dateenntered or not using another CTE

  , cte_date as
( select distinct e.[schema],e.[table],'Y' as has_emp, case when c.[name] is NULL then 'N' else'Y' end as has_date, isnull(c.[name],'') as [date]
  from cte_emp e
  left outer join sys.columns c on e.[object_id] = c.[object_id] and c.[name] like 'DateEntered'
) 
-- Now we can construct our dynamic SQL to go find the results....

  select @sql = isnull(@sql+char(13)+char(10) +' union all ','') 
              + 'select '''+[schema]+''' as [Schema],'''+[table]+''' as [Table],'''+[date]+''' as [Date],'''+[Has_Emp]+''' as [Has_Emp],'''+[Has_Date]+''' as [Has_Date], 
			    case when '''+Has_Date+''' = ''Y'' then (select top 1 ''Y'' from ['+[schema]+'].['+[table]+'] 
				where '+case when len([date])>1 then '['+[date]+']' else 'getdate()-100' end +' > getdate() - 30) else ''NA'' end as Has_Recent_Dates'  from cte_date  
  
print @sql

exec (@sql)

Open in new window

SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

@Mark,
Yours I get this
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@sql".
Msg 137, Level 15, State 2, Line 26
Must declare the scalar variable "@sql".
Msg 137, Level 15, State 2, Line 28
Must declare the scalar variable "@sql".

Open in new window

@Scott,
For yours I get this..
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AddDate'.

Open in new window

Thanks,
Ben
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Sorry, I used "AddDate" for my testing.

Change AddDate to DateEntered and re-run.
Avatar of Mark Wills
Mark Wills
Flag of Australia image
WELL, I dont know what to say.

All versions I have posted work fine on my machine. There is no ')' on line 11 - are you changing anything in the code ?  It is almost as if that first CTE hasnt got its closing bracket.

Azure doesnt have sys.schemas - but we tested and was OK

Could change those (after schemas) joins to inner joins

Hope you have more luck with Scott....
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

In the end all working, thank you my experts!