-1

I'm trying to write a metadata query (sys.tables and sys.columns) that also contains an additional column with an aggregate on that column's data. The output I'm looking for will be a table with these columns:

  • SchemaName
  • TableName
  • ColumnName
  • MaxValueForColumn (or similar)

I know how to get the first three columns by querying metadata, and I know how to write a regular query, but I'm having trouble wrapping my head around how to combine the two tasks into a single output. I think I may need to write a function, but I'm still confused about how to join the sys tables to the regular table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user9211266
  • 1
  • 1
  • 1
    This requires dynamic SQL. You cannot use dynamic SQL in a function. – Gordon Linoff Aug 21 '18 at 23:20
  • It is difficult to see how that information would be useful. But as Gordon said, you will need dynamic sql which may be beyond your abilities. In addition, you appear to desire this in a single result set. That means you will need to convert your values to a single common datatype - most likely string. Do you intend to work with this information afterwards? Converting may complicate that. Do you have binary data to consider? Any varchar(max) columns? And "or similar" is warning sign that you may have chosen a path too quickly. – SMor Aug 22 '18 at 11:52
  • I was just trying to avoid overloading the question with too much info. Basically, I have to analyze a database, on a column by column level, and I'm hoping I can output some of the simplest checks I'll need to do. There are a variety of different operations I'm considering, but the thing I am stuck on is the problem I'll need to solve with dynamic sql. Even if all I get out of this is a check for all-NULL or all single-value columns, it will be worth it as there are hundreds of tables. I figured I'd sort out the process first and figure out what column content analysis I need in the next step – user9211266 Aug 22 '18 at 14:51

1 Answers1

-1

Please try the below query.

select t.name tablename,s.name schemaname,c.name,c.max_length from sys.tables t left join sys.schemas s on t.schema_id=s.schema_id left join sys.columns c on c.object_id=t.object_id Please let me know if your requirement is different

Thanks Sasi

Bhushan
  • 114
  • 12
  • Hi sasi, yes my requirement is different. I don't need to know the max length a column will allow; I need to look at what it actually has in it. Many of the columns in this db are actually NULL, single value, only populated by an old data migration, etc. My end goal is to automate some simple checks on actual contents, with the exact checks yet to be determined by. – user9211266 Aug 22 '18 at 14:54