mssql - sql包含字串




查找包含具有指定名稱的列的所有表-MS SQL Server (19)

是否可以查詢包含列的表名?

LIKE '%myName%'


搜索表格:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

搜索表和視圖:

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

以下查詢將為您提供具有字段名稱(如'%myName')的數據庫的確切表名稱。

SELECT distinct(TABLE_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'

如果你只是想要你可以運行的表名:

select object_name(object_id) from sys.columns
where name like '%received_at%'

如果你需要Schema Name(在很多情況下你會這樣做,因為你會有很多不同的模式,除非你能記住數據庫中的每個表以及它的屬性,這可能是有用的)運行:

select OBJECT_SCHEMA_NAME(object_id),object_name(object_id) from sys.columns
where name like '%received_at%'

最後如果你想要一個更好的格式(雖然這是代碼(在我看來)越來越複雜,容易寫作):

select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columns
where name like '%received_at%'

注意你也可以根據我所擁有的來創建一個函數:

CREATE PROCEDURE usp_tablecheck
--Scan through all tables to identify all tables with columns that have the provided string
--Stephen B
@name nvarchar(200)
AS
SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columns
WHERE name LIKE CONCAT('%',@name,'%')
ORDER BY [Table Name] ASC, [Column] ASC
GO

值得注意的是,concat特性是在2012年添加的。對於2008r2和更早版本,使用+來連接字符串。

自從我發布這個以來,我已經重新格式化了proc。 現在它更高級一些,但看起來很混亂(但它在一個proc中,所以你永遠不會看到它),並且格式化得更好。

該版本允許您將其放在管理數據庫中,然後搜索任何數據庫。 將@db'master'更改為默認數據庫所在的位置(注意:除非您更改字符串連接以使用+運算符,否則使用CONCAT()函數將僅適用於2012+)。

CREATE PROCEDURE [dbo].[usp_tablecheck]
    --Scan through all tables to identify all tables in the specified database with columns that have the provided string
    --Stephen B
    @name nvarchar(200)
    ,@db nvarchar(200) = 'master'
AS
    DECLARE @sql nvarchar(4000) = CONCAT('
        SELECT concat(OBJECT_SCHEMA_NAME(col.object_id,DB_ID(''',@db,''')),''.'',object_name(col.object_id,DB_ID(''',@db,'''))) AS [Table Name]
            ,col.name AS [Column] 
        FROM ',@db,'.sys.columns col
        LEFT JOIN ',@db,'.sys.objects ob 
            ON ob.object_id = col.object_id
        WHERE 
            col.name LIKE CONCAT(''%'',''',@name,''',''%'') 
            AND ob.type =''U''
        ORDER BY [Table Name] ASC
            ,[Column] ASC')
    EXECUTE (@sql)
GO


就像oracle一樣,你可以找到這樣的表格和列:

select table_name, column_name
from user_tab_columns 
where column_name 
like '%myname%';

希望這不是一個重複的答案,但我想要做的是在sql語句中生成一條sql語句,這將允許我搜索我正在查找的值(而不僅僅是具有這些字段名稱的表(因為它通常我需要刪除任何與我正在尋找的列名相關的信息):

  SELECT  'Select * from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

然後,我可以復制並粘貼運行我的第一列“SQLToRun”...然後,我將“Select from *”替換為“Delete from',它允許我刪除對給定ID的任何引用!將這些​​結果寫入文件,以便您讓他們以防萬一。

注意****確保在運行你的刪除語句之前刪除任何bakup表

  SELECT  'Delete from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

您可以通過column_name過濾器從INFORMATION_SCHEMA.COLUMNS中找到它

Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
     From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'

我不知道為什麼很多人建議sys.table with sys.columnssys.table with sys.columns你可以簡單地使用下面的代碼:

Select object_name(object_id) as TableName,* from SYS.columns where name LIKE '%MyName%'

要么

如果您還想要模式名稱:

Select * from  INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME LIKE '%MyName%'

我剛剛嘗試過,並且完美地工作

USE YourDatabseName
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%YourColumnName%'
ORDER BY schema_name, table_name;

只需將YourDatbaseName更改為您的數據庫,將YourcolumnName更改為您正在查找其餘列的名稱即可。

希望這有助於


我用它來達到同樣的目的,它工作的很好:

  select * from INFORMATION_SCHEMA.COLUMNS
  where TABLE_CATALOG= 'theDatabase'
  and COLUMN_NAME like 'theCol%'

獲取完整信息:列名,表名以及表的模式

SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%col_Name%'

這應該工作:

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_name%' )

DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'

SELECT t.name AS Table, c.name AS Column,
ty.name AS Type, c.max_length AS Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.name

SELECT  [TABLE_NAME] ,
        [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;

SELECT col.Name AS ColumnName, tab.Name AS TableName 
FROM sys.columns col  
     JOIN sys.tables tab   
ON col.Object_id = tab.Object_id   
WHERE col.Name LIKE '%MyName%'

SELECT t.name AS table_name, 
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Label%'
ORDER BY schema_name, table_name;

USE AdventureWorks

GO

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
 c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name; 

它來自Pinal Sir Blog


declare @ColumnName nvarchar(max)='QbId'

Select b.Name as TableName from Sys.Columns a
Join Sys.Tables b
on a.Object_Id=b.Object_Id
where [email protected]

select table_name, column_name
from user_tab_columns where column_name like '%myname%';

有用





system-tables