在SQL中按資料型別選擇列

我想撰寫一個通用查詢，它將在許多不同的表上運行。它需要從它運行的任何表中選擇所有列，但有一個問題：選擇必須排除資料型別為“ntext”的列。否則很簡單

select * from <tableName>.

有任何想法嗎？

我能夠創建一個查詢，列出表中不是“ntext”的所有列。不幸的是，我不能將此引數傳遞給另一個選擇，因為它回傳多個結果。

uj5u.com熱心網友回復：

這不是一個好主意，但我沒有更好的方法讓你實作你想要的。

基本上，您在創建查詢以訪問系統物件視圖方面走在了正確的軌道上。然后，您將其轉換為 select 陳述句，并將其傳遞給要迭代的游標。

DECLARE c CURSOR FOR 
SELECT 'SELECT ''' sName '.' tName ''' AS TableName, '  STRING_AGG('[' cName ']',', ')   ' FROM [' sName '].[' tName ']' AS tSQL
  FROM (
        SELECT c.name AS cName, t.name AS tName, s.name AS sName
          FROM sys.tables t
            INNER JOIN sys.columns c
              ON t.object_id = c.object_id
            INNER JOIN sys.systypes st
              ON c.system_type_id = st.xtype
            INNER JOIN sys.schemas s
              ON t.schema_id = s.schema_id
         WHERE st.name NOT IN ('TEXT', 'NTEXT','IMAGE','BINARY')
           AND (st.name NOT IN ('NVARCHAR','VARCHAR') OR c.max_length < 50)
       ) a
 GROUP BY tName, sName
DECLARE @tSql NVARCHAR(MAX)
OPEN c
FETCH NEXT FROM c INTO @tSql
WHILE @@FETCH_STATUS <> -1
BEGIN
 PRINT @tSQL
 EXEC sp_executeSQL @tSQL
 FETCH NEXT FROM c INTO @tSql
END
CLOSE c
DEALLOCATE c

游標很糟糕，你不應該使用它們，除非你沒有太多選擇。警告：如果你有很多表，有很多資料，這將運行很長時間。在您完全測驗并確定它不會導致您的系統出現問題之前，您不應該在生產中使用它，并且您接受這樣做需要您自擔風險。

uj5u.com熱心網友回復：

捎帶@SQLpro 的回答。我希望它更靈活。此版本將允許您輸入帶或不帶架構前綴的表引數。它還允許您不輸入任何引數，如果沒有表引數，查詢將提取所有表。如果在沒有模式的情況下鍵入了表引數，則查詢將在 TableName = @TABLE_NAME 的所有模式上進行過濾。

另一個需要注意的是，當使用 PRINT 時，它確實有一個最大輸出。這可以解釋，雖然您可以做一些魔術來讓 PRINT 吐出無窮無盡的資料，但如果我需要或想要復制每個生成的每個表的 SELECT 陳述句。

下面的示例使用 PRINT。PRINT 有字符限制，因此請參閱如何輕松解決此問題的第二個示例。

/* DynamicSelect.sql 
 | Desc: This version uses PRINT to spit out the SELECT statements.
*/

DECLARE @TABLE_NAME NVARCHAR(261) = 'dbo.CT_DogBreed' --'dbo.CT_DogBreed' 'import.CT_DogBreed'
      , @SQL        NVARCHAR(max) = N''
;

SELECT @SQL = CONCAT(@SQL, N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME, char(13))
    --CONCAT(N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME) AS DynamicSQL
    --, c.TABLE_SCHEMA
    --, c.TABLE_NAME
    --, PARSENAME(@TABLE_NAME, 2) AS SchemaPartFilterOnParameter
    --, PARSENAME(@TABLE_NAME, 1) AS TablePartFilterOnParameter

FROM   INFORMATION_SCHEMA.COLUMNS c
WHERE  DATA_TYPE <> 'ntext'

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 2) IS NOT NULL /* If TRUE then filter on Schema */
             THEN 
                CASE WHEN c.TABLE_SCHEMA = PARSENAME(@TABLE_NAME, 2) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Schema and return 1 for TRUE and not apply Schema Filter - Returns All Schemas */
             ELSE 1 END

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 1) IS NOT NULL /* If TRUE then filter on Table */
             THEN 
                CASE WHEN c.TABLE_NAME = PARSENAME(@TABLE_NAME, 1) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Table and return 1 for TRUE and not apply Table Filter - Returns All Tables  */
             ELSE 1 END

GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME

PRINT @SQL

--EXEC (@SQL);

不使用 PRINT - 將查詢結果設定為文本。PRINT 有字符限制。

/* DynamicSelect.sql 
 | Desc: Using this version you can output the data to the Grid or
 |       set your Query Ouput from the default GRID output to TEXT.
 |       Comment out all fields except the DynamicSQL Column if you output as TEXT.
*/

DECLARE @TABLE_NAME NVARCHAR(261) = 'dbo.CT_DogBreed' --'dbo.CT_DogBreed' 'import.CT_DogBreed'
      , @SQL        NVARCHAR(max) = N''
;

SELECT --@SQL = CONCAT(@SQL, N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME, char(13))
    CONCAT(N'SELECT ', STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ' ), ' FROM ', c.TABLE_SCHEMA ,'.', c.TABLE_NAME) AS DynamicSQL
    , c.TABLE_SCHEMA
    , c.TABLE_NAME
    , PARSENAME(@TABLE_NAME, 2) AS SchemaPartFilterOnParameter
    , PARSENAME(@TABLE_NAME, 1) AS TablePartFilterOnParameter

FROM   INFORMATION_SCHEMA.COLUMNS c
WHERE  DATA_TYPE <> 'ntext'

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 2) IS NOT NULL /* If TRUE then filter on Schema */
             THEN 
                CASE WHEN c.TABLE_SCHEMA = PARSENAME(@TABLE_NAME, 2) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Schema and return 1 for TRUE and not apply Schema Filter - Returns All Schemas */
             ELSE 1 END

    AND  1 = CASE WHEN PARSENAME(@TABLE_NAME, 1) IS NOT NULL /* If TRUE then filter on Table */
             THEN 
                CASE WHEN c.TABLE_NAME = PARSENAME(@TABLE_NAME, 1) THEN 1 ELSE 0 END
             /* Else if False perform no filter on Table and return 1 for TRUE and not apply Table Filter - Returns All Tables  */
             ELSE 1 END

GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME

--PRINT @SQL

--EXEC (@SQL);

如何將查詢結果從默認網格設定為文本的螢屏截圖。導航到查詢 --> 結果到 --> 結果到文本。

將 PRINT 和 Query 結果顯示為文本的 Gif。

uj5u.com熱心網友回復：

這是執行此操作的最短代碼：

DECLARE @TABLE_NAME NVARCHAR(261) = 'dbo.sys_dm_db_missing_index_details'
               ,@SQL NVARCHAR(max) = N'';
SELECT @SQL = N'SELECT '   STRING_AGG('['   CAST(COLUMN_NAME AS NVARCHAR(max))   ']', ', ')   ' FROM '   @TABLE_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  DATA_TYPE <> 'ntext'
  AND   TABLE_SCHEMA = PARSENAME(@TABLE_NAME, 2)
  AND   TABLE_NAME = PARSENAME(@TABLE_NAME, 1)
EXEC (@SQL);

您只需替換 @TABLE_NAME 變數中的表名即可在特定表上使用它。

標籤：sql服务器tsqlsql数据类型

上一篇：使用存盤程序的多重搜索過濾器

下一篇：SQL從一個句子中搜索另一個句子中的單詞