반응형
SQL Server의 모든 데이터베이스에있는 모든 테이블의 열 이름을 찾는 방법
모든 데이터베이스의 모든 테이블 에서 모든 열 이름을 찾고 싶습니다 . 나를 위해 할 수있는 쿼리가 있습니까? 데이터베이스는 Microsoft SQL Server 2000입니다.
이 시도 :
select
o.name,c.name
from sys.columns c
inner join sys.objects o on c.object_id=o.object_id
order by o.name,c.column_id
결과 열 이름은 다음과 달라집니다.
select
o.name as [Table], c.name as [Column]
from sys.columns c
inner join sys.objects o on c.object_id=o.object_id
--where c.name = 'column you want to find'
order by o.name,c.name
또는 자세한 내용은 다음을 참조하세요.
SELECT
s.name as ColumnName
,sh.name+'.'+o.name AS ObjectName
,o.type_desc AS ObjectType
,CASE
WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
ELSE t.name
END AS DataType
,CASE
WHEN s.is_nullable=1 THEN 'NULL'
ELSE 'NOT NULL'
END AS Nullable
,CASE
WHEN ic.column_id IS NULL THEN ''
ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
END
+CASE
WHEN sc.column_id IS NULL THEN ''
ELSE ' computed('+ISNULL(sc.definition,'')+')'
END
+CASE
WHEN cc.object_id IS NULL THEN ''
ELSE ' check('+ISNULL(cc.definition,'')+')'
END
AS MiscInfo
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
LEFT OUTER JOIN sys.identity_columns ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
LEFT OUTER JOIN sys.computed_columns sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
ORDER BY sh.name+'.'+o.name,s.column_id
편집
다음은 모든 데이터베이스의 모든 열을 가져 오는 기본 예입니다.
DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.column_id
from '+d.name+'.sys.columns c
inner join '+d.name+'.sys.objects o on c.object_id=o.object_id
INNER JOIN '+d.name+'.sys.schemas sh on o.schema_id=sh.schema_id
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
SQL Server 2000 버전 편집
DECLARE @SQL varchar(8000)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.colid
from '+d.name+'..syscolumns c
inner join sysobjects o on c.id=o.id
INNER JOIN sysusers sh on o.uid=sh.uid
'
FROM master.dbo.sysdatabases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
편집
몇 가지 의견을 바탕으로 sp_MSforeachdb다음을 사용하는 버전이 있습니다 .
sp_MSforeachdb 'select
''?'' AS DatabaseName, o.name AS TableName,c.name AS ColumnName
from sys.columns c
inner join ?.sys.objects o on c.object_id=o.object_id
--WHERE ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')
order by o.name,c.column_id'
사용하지 않는 이유
Select * From INFORMATION_SCHEMA.COLUMNS
당신은 그것을 DB 특정으로 만들 수 있습니다
Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS
SELECT *
FROM information_schema.columns
WHERE column_name = 'My_Column'
USE [db_name]이 쿼리 전에 현재 데이터베이스 이름을 설정해야합니다 .
당신을위한 더 나은 방법
sp_MSForEachDB @command1='USE ?;
SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ''%ColumnNameHere%'''
사용자 @KM이 베스트 답변을 사실.
나는 이것을 사용한다 :
Declare @Table_Name VarChar(100) ,@Column_Name VarChar(100)
Set @Table_Name = ''
Set @Column_Name = ''
Select
RowNumber = Row_Number() Over( PARTITION BY T.[Name] Order By T.[Name],C.column_id ),
SCHEMA_NAME( T.schema_id ) As SchemaName ,
T.[Name] As Table_Name ,
C.[Name] As Field_Name ,
sysType.name ,
C.max_length , C.is_nullable , C.is_identity , C.scale , C.precision
From Sys.Tables As T
Left Join Sys.Columns As C On ( T.[Object_Id] = C.[Object_Id] )
Left Join sys.types As sysType On ( C.user_type_id = sysType.user_type_id )
Where ( Type = 'U' )
And ( C.Name Like '%' + @Column_Name + '%' )
And ( T.Name Like '%' + @Table_Name + '%' )
일반적으로 커서 사용을 피하기 위해 할 수있는 모든 작업을 시도하지만 다음 쿼리를 통해 필요한 모든 것을 얻을 수 있습니다.
--Declare/Set required variables
DECLARE @vchDynamicDatabaseName AS VARCHAR(MAX),
@vchDynamicQuery As VARCHAR(MAX),
@DatabasesCursor CURSOR
SET @DatabasesCursor = Cursor FOR
--Select * useful databases on the server
SELECT name
FROM sys.databases
WHERE database_id > 4
ORDER by name
--Open the Cursor based on the previous select
OPEN @DatabasesCursor
FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert the select statement into @DynamicQuery
--This query will select the Database name, all tables/views and their columns (in a comma delimited field)
SET @vchDynamicQuery =
('SELECT ''' + @vchDynamicDatabaseName + ''' AS ''Database_Name'',
B.table_name AS ''Table Name'',
STUFF((SELECT '', '' + A.column_name
FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS A
WHERE A.Table_name = B.Table_Name
FOR XML PATH(''''),TYPE).value(''(./text())[1]'',''NVARCHAR(MAX)'')
, 1, 2, '''') AS ''Columns''
FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS B
WHERE B.TABLE_NAME LIKE ''%%''
AND B.COLUMN_NAME LIKE ''%%''
GROUP BY B.Table_Name
Order BY 1 ASC')
--Print @vchDynamicQuery
EXEC(@vchDynamicQuery)
FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
END
CLOSE @DatabasesCursor
DEALLOCATE @DatabasesCursor
GO
원하는 경우 특정 테이블 및 / 또는 열을 검색 할 수 있도록 기본 쿼리에 절을 추가했습니다 (예 : B.TABLE_NAME LIKE ''%% ''AND B.COLUMN_NAME LIKE ''%% ''). .
모두에게 : 모든 게시물과 댓글에 감사드립니다. 일부는 훌륭하지만 일부는 더 좋습니다.
첫 번째 큰 펼쳐는 필요한 전달하기 때문에 좋습니다. 가장 빠른 상세한 것은 INFORMATION_SCHEMA.COLUMNS에서 선택하기위한 하나의 제안입니다 ..
내 필요는 거의 동일한 이름과 여러 데이터베이스의 모든 잘못된 열을 찾는 것이 었습니다. Sooo, 두 버전을 모두 만들었습니다 (아래 참조). 아래 두 펼쳐 중 하나가 작동하고 몇 초 만에 상품을 배달합니다.
이 링크에 대한 다른 게시물의 가정은 첫 번째 코드 예제가 for-each-database와 함께 사용할 수 있습니다. 정보가 "fedb"를 사용하면 올바른 결과가 생성되지 않고 액세스가되지 않습니다. 그래서 나는 CURSOR를 사용하여 데이터베이스를 수집하고 오프라인 인 처리 무시합니다.이 경우 유틸리티는 동일한 용도로 사용됩니다.
요컨대, 나는 모든 사람의 게시물을 읽고 게시물의 모든 수정 사항을 통합하고 다른 사람들의 매우 설득력있는 두 개를 좋은 작품으로 만들었습니다. 아래 두 가지를 모두 포함하고있는 링크를 통해 액세스 할 수있는 OneDrive.com의 공용 폴더에 펼쳐 파일을 배치했습니다. http://1drv.ms/1vr8yNX
즐겨! 행크 프리먼
수석 수준 -SQL Server DBA- 데이터 설계자
시도 시도 시도 ...
---------------------------
--- 1st example (works) ---
---------------------------
Declare
@DBName sysname
,@SQL_String1 nvarchar(4000)
,@SQL_String2 nvarchar(4000)
,@ColumnName nvarchar(200)
--set @ColumnName = 'Course_ID'
-------- Like Trick --------
-- IF you want to add more the @ColumnName so it looks like Course_ID,CourseID
-- then add an additional pairing of +''','''+'NewColumnSearchIDValue'
----------------------------
set @ColumnName = 'Course_ID' +''','''+'CourseID'
--select @ColumnName
-----
Declare @Column_Info table
(
[DatabaseName] nvarchar(128) NULL,
[ColumnName] sysname NULL,
[ObjectName] nvarchar(257) NOT NULL,
[ObjectType] nvarchar(60) NULL,
[DataType] nvarchar(151) NULL,
[Nullable] varchar(8) NOT NULL,
[MiscInfo] nvarchar(MAX) NOT NULL
)
--------------
Begin
set @SQL_String2 = 'SELECT
DB_NAME() as ''DatabaseName'',
s.name as ColumnName
,sh.name+''.''+o.name AS ObjectName
,o.type_desc AS ObjectType
,CASE
WHEN t.name IN (''char'',''varchar'') THEN t.name+''(''+CASE WHEN s.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),s.max_length) END+'')''
WHEN t.name IN (''nvarchar'',''nchar'') THEN t.name+''(''+CASE WHEN s.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),s.max_length/2) END+'')''
WHEN t.name IN (''numeric'') THEN t.name+''(''+CONVERT(varchar(10),s.precision)+'',''+CONVERT(varchar(10),s.scale)+'')''
ELSE t.name
END AS DataType
,CASE
WHEN s.is_nullable=1 THEN ''NULL''
ELSE ''NOT NULL''
END AS Nullable
,CASE
WHEN ic.column_id IS NULL THEN ''''
ELSE '' identity(''+ISNULL(CONVERT(varchar(10),ic.seed_value),'''')+'',''+ISNULL(CONVERT(varchar(10),ic.increment_value),'''')+'')=''+ISNULL(CONVERT(varchar(10),ic.last_value),''null'')
END
+CASE
WHEN sc.column_id IS NULL THEN ''''
ELSE '' computed(''+ISNULL(sc.definition,'''')+'')''
END
+CASE
WHEN cc.object_id IS NULL THEN ''''
ELSE '' check(''+ISNULL(cc.definition,'''')+'')''
END
AS MiscInfo
into ##Temp_Column_Info
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
LEFT OUTER JOIN sys.identity_columns ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
LEFT OUTER JOIN sys.computed_columns sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
--------------------------------------------
--- DBA - Hank 12-Feb-2015 added this specific where statement
-- where Upper(s.name) like ''COURSE%''
-- where Upper(s.name) in (''' + @ColumnName + ''')
-- where Upper(s.name) in (''cycle_Code'')
-- ORDER BY sh.name+''.''+o.name,s.column_id
order by 1,2'
--------------------
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
--select * from sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
--select @DBName as '@DBName';
Set @SQL_String1 = 'USE [' + @DBName + ']'
set @SQL_String1 = @SQL_String1 + @SQL_String2
EXEC sp_executesql @SQL_String1;
--
insert into @Column_Info
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
CLOSE DB_cursor;
Deallocate DB_cursor;
---
select * from @Column_Info order by 2,3
----------------------------
end
---------------------------
Below is the Second script..
---------------------------
--- 2nd example (works) ---
---------------------------
-- This is by far the best/fastes of the lot for what it delivers.
--Select * into dbo.hanktst From Master.INFORMATION_SCHEMA.COLUMNS
--FileID: scriptGet_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
----------------------------------------
--FileID: scriptGet_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
-- Utility to find all columns in all databases or find specific with a like statement
-- Look at this line to find a: --> set @SQL_String2 = ' select * into ##Temp_Column_Info....
----------------------------------------
---
SET NOCOUNT ON
begin
Declare @hanktst TABLE (
[TABLE_CATALOG] NVARCHAR(128) NULL
,[TABLE_SCHEMA] NVARCHAR(128) NULL
,[TABLE_NAME] sysname NOT NULL
,[COLUMN_NAME] sysname NULL
,[ORDINAL_POSITION] INT NULL
,[COLUMN_DEFAULT] NVARCHAR(4000) NULL
,[IS_NULLABLE] VARCHAR(3) NULL
,[DATA_TYPE] NVARCHAR(128) NULL
,[CHARACTER_MAXIMUM_LENGTH] INT NULL
,[CHARACTER_OCTET_LENGTH] INT NULL
,[NUMERIC_PRECISION] TINYINT NULL
,[NUMERIC_PRECISION_RADIX] SMALLINT NULL
,[NUMERIC_SCALE] INT NULL
,[DATETIME_PRECISION] SMALLINT NULL
,[CHARACTER_SET_CATALOG] sysname NULL
,[CHARACTER_SET_SCHEMA] sysname NULL
,[CHARACTER_SET_NAME] sysname NULL
,[COLLATION_CATALOG] sysname NULL
,[COLLATION_SCHEMA] sysname NULL
,[COLLATION_NAME] sysname NULL
,[DOMAIN_CATALOG] sysname NULL
,[DOMAIN_SCHEMA] sysname NULL
,[DOMAIN_NAME] sysname NULL
)
Declare
@DBName sysname
,@SQL_String2 nvarchar(4000)
,@TempRowCnt varchar(20)
,@Dbug bit = 0
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
set @SQL_String2 = ' select * into ##Temp_Column_Info from [' + @DBName + '].INFORMATION_SCHEMA.COLUMNS
where UPPER(Column_Name) like ''COURSE%''
;'
if @Dbug = 1 Select @SQL_String2 as '@SQL_String2';
EXEC sp_executesql @SQL_String2;
insert into @hanktst
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
select * from @hanktst order by 4,2,3
CLOSE DB_cursor;
Deallocate DB_cursor;
set @TempRowCnt = (select cast(count(1) as varchar(10)) from @hanktst )
Print ('Rows found: '+ @TempRowCnt +' end ...')
end
--------
약간의 개선
-> 이전 답변에 모든 결과가 표시되지 않습니다.
-> 열 이름 변수를 설정하여 열 이름으로 필터링 가능
DECLARE @columnname nvarchar(150)
SET @columnname=''
DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name+'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS as name,c.name COLLATE SQL_Latin1_General_CP1_CI_AS as columnname,c.column_id
from '+d.name+'.sys.columns c
inner join '+d.name+'.sys.objects o on c.object_id=o.object_id
INNER JOIN '+d.name+'.sys.schemas sh on o.schema_id=sh.schema_id
where c.name like ''%'+@columnname+'%'' and sh.name<>''sys''
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
아래 쿼리를 시도하십시오.
DECLARE @Query VARCHAR(max)
SELECT @Query = 'USE ? SELECT ''?'' AS DataBaseName,
sys.columns.name AS ColumnName ,
sys.tables.name AS TableName ,
schema_name (sys.tables.schema_Id) AS schemaName
FROM sys.columns
JOIN sys.tables
ON sys.columns.object_id = sys.tables.object_id
WHERE sys.columns.name = ''id'' '
EXEC SP_MSFOREACHDB @Query
모든 데이터베이스의 ID 열을 포함하는 테이블 목록을 제공합니다.
다음 모든 쿼리가 데이터베이스의 테이블에있는 모든 열 이름을 제공한다는 것을 깨달았습니다 (SQL SERVER 2017).
SELECT DISTINCT NAME FROM SYSCOLUMNS
ORDER BY Name
또는 간단하게
SELECT Name FROM SYSCOLUMNS
두어 된 이름을 신경 쓰지 않는 경우.
또 다른 옵션은 SELECT 컬럼 이름 INFORMATION_SCHEMA
SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY column_name
일반적으로 TableName 과 ColumnName 개미를 사용하는 것이 더 흥미 롭습니다 . 아래 쿼리가이를 수행합니다.
SELECT
Object_Name(Id) As TableName,
Name As ColumnName
FROM SysColumns
결과는 다음과 가변적입니다.
TableName ColumnName
0 Table1 column11
1 Table1 Column12
2 Table2 Column21
3 Table2 Column22
4 Table3 Column23
SELECT sys.columns.name AS ColumnName, tables.name AS TableName
FROM sys.columns
JOIN sys.tables ON sys.columns.object_id = tables.object_id
나는 다음을 사용했다 :
EXEC sp_MSforeachdb 'Use ? Select * From INFORMATION_SCHEMA.COLUMNS '
내가 필요한 것을 위해 작동하는 것입니다.
저와 같은 DB 서버에서 콜 레이션 재미가있는 사람들을 위해 KM의 솔루션에 대한 사소한 개선 ....
DECLARE @SQL varchar(max)=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name +'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.column_id
from '+d.name +'.sys.columns c
inner join sys.objects o on c.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
(아직도 뷰로 감싸 질 수있는 방법을 찾을 수 있기를 바라며 살고 있습니다.)
반응형
'ProgramingTip' 카테고리의 다른 글
| 자체 크기 조정 UITableViewCell 내부의 여러 UILabel (0) | 2020.12.03 |
|---|---|
| Java ArrayList의 시간 짜고 (0) | 2020.12.03 |
| 버그 오류 : werkzeug.routing.BuildError (0) | 2020.12.03 |
| Android 드로어 블의 색상 수정 (0) | 2020.12.03 |
| UIBarButton (0) | 2020.12.03 |