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
LVL 4
bfuchsAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
This is part of your need:
SELECT
        T.name TableName
      , i.Rows NumberOfRows
      , coalesce(hc1.has_col1,0) has_col1
      , coalesce(hc2.has_col2,0) has_col2
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
outer apply (
    select 1 as has_col1 
    from sys.columns c
    where name like 'Emp%'
    and T.OBJECT_ID = C.OBJECT_ID
    ) hc1
outer apply (
    select 1 as has_col2 
    from sys.columns c
    where name = 'DateEntered'
    and T.OBJECT_ID = C.OBJECT_ID
    ) hc2
WHERE       indid IN (0,1)
ORDER BY    i.Rows DESC,T.name
;

Open in new window

I have not attempted to include this: "and there are records within last 30 days " which requires "dynamic sql"
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
bfuchsAuthor Commented:
@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
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
bfuchsAuthor Commented:
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
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
bfuchsAuthor Commented:
@Mark,

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

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
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

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
This is just not that difficult:

DECLARE @sql varchar(max)
SET @sql = ''

;WITH CteTables AS (
SELECT      OBJECT_NAME(T.object_id) AS TableName,
            MAX(CASE WHEN C.name LIKE 'Emp%' THEN 'True' ELSE 'False' END) AS HasEmpColumn,
            MAX(CASE WHEN C.name = 'DateEntered' THEN 'True' ELSE 'False' END) AS HasDateEnteredColumn
FROM        sys.columns C
INNER JOIN  sys.tables T ON T.object_id = C.object_id
WHERE       C.name LIKE 'Emp%' OR C.name = 'DateEntered'
GROUP BY    T.object_id
)
SELECT      @sql = @sql + 'UNION ALL SELECT ''' + T.TableName + ''', ''' + T.HasEmpColumn + ''', ' +
            CASE WHEN T.HasDateEnteredColumn = 'False' THEN '''False''' ELSE
                'CASE WHEN EXISTS(SELECT 1 FROM dbo.[' + T.TableName + '] T2 ' +
                'WHERE T2.AddDate >= DATEADD(DAY, -30, CAST(GETDATE() AS date))) ' +
                'THEN ''True'' ELSE ''False'' END ' END
FROM        CteTables T

SET @sql = 'SELECT * FROM (' + STUFF(@sql, 1, 10, '') + ') AS data( TableName, HasEmpColumn, HasRecentDateEntered ) ORDER BY TableName, HasEmpColumn, HasRecentDateEntered'

--SELECT @sql --optional
EXEC(@sql)
0
 
bfuchsAuthor Commented:
@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
0
 
Scott PletcherSenior DBACommented:
Sorry, I used "AddDate" for my testing.

Change AddDate to DateEntered and re-run.
0
 
Mark WillsTopic AdvisorCommented:
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....
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Lets remove / avoid schemas
declare @sql varchar(max) 

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

   select Distinct 'dbo' as [schema]
        ,t.[name] as [table]
        ,t.[schema_id] 
        ,t.[object_id]
  From sys.tables t 
  inner 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

0
 
bfuchsAuthor Commented:
In the end all working, thank you my experts!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.