Using INFORMATION_SCHEMA.COLUMNS in SQL to search for an example from a table in a SQL Server-CodePudding

Using the table INFORMATION_SCHEMA.COLUMNS in SQL can obtain the following:

TABLE_NAME, COLUMN_NAME
mytab0, mytab0_col0
mytab0, mytab0_col1
.
.
.
mytab1, mytab1_col0
mytab1, mytab1_col1
.
.
.

SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

I have tried various ways, functions and CTEs to generate a new column, say, "example" that will use the values from TABLE_NAME and COLUMN_NAME to conduct what is equivalent to a select statement that will yield the result of, for example:

SELECT TOP(1) mytab0_col0 WHERE mytab0_col0 IS NOT NULL FROM mytab0

This output would then need to go into an example column.

I have tried to use a function:

CREATE FUNCTION dbo.foo(@input_table VARCHAR(250), @input_columns VARCHAR(250))
RETURNS VARCHAR(250)


AS BEGIN
    DECLARE @return_value VARCHAR(250)
    DECLARE @tab_name VARCHAR(250)

    SET @tab_name = @input_table
    
    SELECT @return_value = @input_columns
    FROM @tab_name
    WHERE @input_columns IS NOT NULL

   
    RETURN @return_value

END;

This fails because the FROM @tab_name expects an actual table and not a varchar. Doing this just won't work for many reasons, although this does:

declare @xvar as VARCHAR(250)
select @xvar = [mytab0_col0]
from mytab0
where [mytab0_col0] is not null
print @xvar

I have also tried to create a CTE and then INSERT INTO or use a function with a CTE which was unsuccessful.

CodePudding user response：

Honestly, this is a mess, but it works. Though I could try to explain, it's more important you understand how this works yourself, and you need to take the time to do so. Dynamic SQL is not an easy tool to use and in the wrong hands, dangerous. I've made this as safe as possible from injection attacks, so if you are going to change it, ensure that you don't open avenues.

DECLARE @SchemaName sysname,
        @TableName sysname;

SET @SchemaName = N'dbo';
SET @TableName = N'YourTable';

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

DECLARE @SelectDelimiter nvarchar(20) = N','   @CRLF   N'             ',
        @ValuesDelimiter nvarchar(50) = N','   @CRLF   N'                         ';

SELECT @SQL = N'SELECT s.[name] AS SchemaName,'   @CRLF  
              N'       t.[name] AS TableName,'   @CRLF  
              N'       V.ColumnName,'   @CRLF  
              N'       ct.[name] AS DataType,'   @CRLF  
              N'       V.MaxColumnValue'   @CRLF  
              N'FROM (SELECT '   STRING_AGG(CONVERT(nvarchar(MAX),N'MAX('   QUOTENAME(c.[name])   N') AS '   QUOTENAME(c.[name])),@SelectDelimiter)   @CRLF  
              N'      FROM '   QUOTENAME(s.[name])   N'.'   QUOTENAME(t.[name])   N') YT'   @CRLF   
              N'      CROSS APPLY (VALUES'   STRING_AGG(CONVERT(nvarchar(MAX),N'(N'   QUOTENAME(c.[name],'''')   N', CONVERT(nvarchar(MAX),YT.'   QUOTENAME(c.[name])   N'))'),@ValuesDelimiter)   N')V(ColumnName,MaxColumnValue)'   @CRLF  
              N'      JOIN sys.schemas s ON s.[name] = @SchemaName'   @CRLF  
              N'      JOIN sys.tables t ON s.schema_id = t.schema_id'   @CRLF  
              N'      JOIN sys.columns c ON t.object_id = c.object_id'   @CRLF  
              N'                        AND c.[name] = V.ColumnName'   @CRLF   
              N'      JOIN sys.types ct ON c.system_type_id = ct.system_type_id'   @CRLF  
              N'WHERE t.[name] = @TableName;'
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 s.[name] = @SchemaName
  AND t.[name] = @TableName
GROUP BY s.[name],
         t.[name];

--SELECT @SQL; --Your debugging friend

EXEC sys.sp_executesql @SQL, N'@SchemaName sysname, @TableName sysname', @SchemaName, @TableName;