SQL Server – How to Update All Tables Containing a Column Name

Since you are on SQL Server 2012, best is to use the new DMF sys.dm_exec_describe_first_result_set. This dmf will take a transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.

so for example, you want to find out the columns names along with the datatypes and precison for sys.database_files, simply use

SELECT 
    name, 
    system_type_name, 
    is_nullable
 -- add/remove columns as per your need ....
FROM 
    sys.dm_exec_describe_first_result_set
    (
        N'SELECT * FROM sys.database_files;', NULL, 0
    ) AS f
ORDER BY
    column_ordinal;

Below are the results :

For versions below sql 2012:

SELECT object_NAME(c.object_id) AS OBJECT_NAME
    ,c.NAME AS COLUMN_NAME
    ,t.NAME AS DATA_TYPE
    ,c.max_length AS MAX_LENGTH
FROM sys.all_columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE object_name(c.object_id) = 'database_files'

Excellent post is written by Aaron Bertrand -- SQL Server v.Next (Denali) :Metadata enhancements which explains this in much details. Also, refer to the CAVEATS in the section down the article.

I see many suggestions for using information_schema.columns for getting table names for user databases/tables.

Avoid using INFORMATION_SCHEMA views. Worth reading Aaron's post on The case against INFORMATION_SCHEMA views.

Currently there is no syntax directly supporting what you are trying to do. As you probably know, names cannot be parametrised in a SQL statement. That means that when you need to substitute names from column values of another table, you have to use dynamic SQL: first build the query string and then execute it. There is just no working around using dynamic SQL in such cases. Furthermore, you have already established for yourself that you cannot use dynamic SQL in a function. So there you are, seemingly stumped.

However, if you insist on using a single SELECT statement for this, there is one way – provided you agree to bend over backwards slightly to achieve the goal, that is. And accept a major limitation of the method.

The solution involves creation of a loopback linked server and using the OPENQUERY function. But first you will need to make sure your dynamic SQL solution works as it is. For the purpose of this answer, I am going to assume that the dynamic SQL looks like this:

DECLARE @sql nvarchar (max) = '', @sqltemplate nvarchar(max) =
'UNION ALL
SELECT
  Column_Name = ''{Column_Name}'',
  Table_Name  = ''{Table_Name}'',
  Max_Length  = MAX(LEN([{Column_Name}]))
FROM
  [oil stop].dbo.[{Table_Name}]
';
SELECT
  @sql += REPLACE(
            REPLACE(
              @sqltemplate,
              '{Column_Name}',
              Column_Name
            ),
            '{Table_Name}',
            Table_Name
          )
FROM
  tempdb.dbo.YourMetaDataTable
;
SET @sql = STUFF(@sql, 1, 9, '');  -- remove the leading UNION ALL
EXECUTE sp_executesql @sql;

Once you have verified the script is working, and made sure the loopback linked server is created, just put the script inside the OPENQUERY function like this:

SELECT
  *
FROM
  OPENQUERY(
    YourLinkedServerName,
    '...'  -- the dynamic SQL script
  )
;

Remember to double each quotation mark (apostrophe) inside the script.

One other important change you will likely need to make is to add a WITH RESULT SETS clause to the EXECUTE statement to describe the result set, so that OPENQUERY can process the output correctly for you. When describing the result set, you will likely just repeat the same type for Column_Name and Table_Name as defined for them in the metadata table. For the example below I am assuming the type to be sysname in both cases. And as for the Max_Length column, I believe int would work well there. So, the modified EXECUTE statement would look like this:

EXECUTE sp_executesql @sql
WITH RESULT SETS
(
  (Column_Name sysname, Table_Name sysname, Max_Length int)
);

For completeness, and to make the lack of elegance in this solution more evident for the wider audience, this is what the final query would look like:

SELECT
  *
FROM
  OPENQUERY(
    [OIL STOP],
    'DECLARE @sql nvarchar (max) = '''', @sqltemplate nvarchar(max) =
    ''UNION ALL
    SELECT
      Column_Name = ''''{Column_Name}'''',
      Table_Name  = ''''{Table_Name}'''',
      Max_Length  = MAX(LEN([{Column_Name}]))
    FROM
      [oil stop].dbo.[{Table_Name}]
    '';
    SELECT
      @sql += REPLACE(
                REPLACE(
                  @sqltemplate,
                  ''{Column_Name}'',
                  Column_Name
                ),
                ''{Table_Name}'',
                Table_Name
              )
    FROM
      tempdb.dbo.MetaData
    ;
    SET @sql = STUFF(@sql, 1, 9, '''');  -- remove the leading UNION ALL
    EXECUTE sp_executesql @sql
    WITH RESULT SETS
    (
      (Column_Name sysname, Table_Name sysname, Max_Length int)
    );
    '
  )
;

The main problem, though, is that the query above still cannot be parametrised, and that is the principal limitation I was talking about. Even though the OPENQUERY script is specified as a string literal, it can only be a single string literal – not a variable, not a complex expression. That means that if you want to apply the query to a different subset of rows of the metadata table, you will have to use a new script for that.