Cómo encontrar nombres de columna para todas las tablas en todas las bases de datos en SQL Server
Quiero encontrar todos los nombres de columna en todas las tablas en todas las bases de datos. ¿Hay alguna consulta que pueda hacer eso por mí? La base de datos es Microsoft SQL Server 2000.
10 answers
Prueba esto:
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
O para más detalles:
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
EDITAR
aquí hay un ejemplo básico para obtener todas las columnas en todas las bases de datos:
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)
EDITAR SQL Server 2000 version
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)
EDITAR
basado en algunos comentarios, aquí hay una versión usando 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'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-27 13:01:33
SELECT *
FROM information_schema.columns
WHERE column_name = 'My_Column'
Debe establecer su nombre de base de datos actual con USE [db_name]
antes de esta consulta.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-10-27 09:00:07
¿Por qué no usar
Select * From INFORMATION_SCHEMA.COLUMNS
Puede hacerlo DB específico con
Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-28 13:12:40
Mejor manera para usted
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%'''
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-11-18 11:03:18
El Usuario @KM dice la mejor respuesta.
Yo Uso Esto:
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 + '%' )
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-09-25 12:31:49
Normalmente trato de hacer todo lo posible para evitar el uso de cursores, pero la siguiente consulta te dará todo lo que necesitas:
--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
Agregué una cláusula where en la consulta principal (por ejemplo: B. NOMBRE_ TABLA COMO "%%" Y B. NOMBRE_ COLUMNA COMO "%%") para que pueda buscar tablas y/o columnas específicas si lo desea.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-05-16 20:22:20
A todos: Gracias por todo el post y comentarios algunos son buenos, pero algunos son mejores.
El primer script grande es bueno porque entrega justo lo que se necesita. La más rápida y detallada es la única sugerencia para seleccionar de INFORMATION_SCHEMA.COLUMNA..
Mi necesidad era encontrar todas las columnas errantes de aproximadamente el mismo nombre y Varias bases de datos.. Sooo, hice mis versiones de ambos (ver más abajo) ...Cualquiera de estos dos trabajos de script a continuación y entregar los bienes en segundo.
La suposición en otras publicaciones en este enlace, es que el primer ejemplo de código se puede utilizar con éxito con for-each-database, es para mí, no deseable. Esto se debe a que la información está dentro de la base de datos específica y el simple uso del "fedb" no produce los resultados correctos, simplemente no da acceso. SOOO a que es por eso que uso un CURSOR para recoger las bases de datos e ignorar los que están Fuera de línea, que en este caso, un script de utilidad, es un buen uso de la misma.
En pocas palabras, leí el post de todos, incorporé toda la corrección de los posts e hice lo que son dos guiones muy elocuentes de otras buenas obras. Enumeré ambos a continuación y también he colocado el archivo de script en mi carpeta pública en OneDrive.com a la que puedes acceder con este enlace: http://1drv.ms/1vr8yNX
Disfrute ! Hank Freeman
Nivel superior-DBA de SQL Server-Arquitecto de datos
Pruébalos por separado...
---------------------------
--- 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: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
----------------------------------------
--FileID: SCRIPT_Get_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
--------
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-02-13 18:07:06
Pruebe la siguiente consulta
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
Proporciona una lista de tablas que contienen la columna ID de todas las bases de datos.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-15 09:11:55
Refinamiento menor en la solución de KM para aquellos como yo que tienen diversión de colación en su servidor de base de datos....
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)
(Todavía viviendo con la esperanza de que encontraremos una manera de hacer esto que se pueda envolver en una vista.)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-06-13 22:47:29
Algunas mejoras menores
- >las respuestas anteriores no muestran todos los resultados
- > es posible filtrar el nombre de la columna configurando la variable nombre de la columna
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-10-25 20:10:43