Tabellen und Spalten in Datenbank filtern

1. AZConAdmin sagte:

   Juni 9, 2020 um 1:47 pm

   SELECT T1.object_id, T1.name AS Spaltenname, T2.name AS Tabelle,T2.schema_id,T3.name ,
   SUM([Partitions].[rows]) AS T
   FROM sys.columns T1
   LEFT JOIN sys.tables AS T2
   ON T1.object_id = t2.object_id
   LEFT JOIN sys.schemas AS T3 ON T2.schema_id = T3.schema_id
   LEFT JOIN sys.partitions AS [Partitions]
   ON T2.[object_id] = [Partitions].[object_id]
   AND [Partitions].index_id IN ( 0, 1 )
   where T1.name like ‘LEISTUNGARTID’
   and T2.name not like ‘TMP%’
   GROUP BY
   T1.object_id,
   T1.name ,
   T2.name,T2.schema_id,T3.name
   HAVING SUM([Partitions].[rows])>0

   Zum Antworten anmelden
2. AZConAdmin sagte:

   Februar 1, 2022 um 5:44 am

   // alle Textspalten in einer Datenbank finden
   SELECT *
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
   DATA_TYPE =’text’
   and TABLE_SCHEMA not in (‘PEP’,’PPS’,’PFDOK’,’BEVIA’,’KORE’,’ANL’,’MTEMP’)
   order by TABLE_SCHEMA

   Zum Antworten anmelden
3. AZConAdmin sagte:

   August 23, 2024 um 11:56 am

   Mit den ausgewählten Spalten eine Tabelle abfragen:

   declare @sql nvarchar(max)
   set @sql = ‘select ‘
   select @sql = @sql + ‘[‘ + column_name +’],’
   from information_schema.columns
   where table_name=’D_Produktvarianten’ and column_name like ‘a%’
   set @sql = left(@sql,len(@sql)-1) — remove trailing comma
   set @sql = @sql + ‘ from D_Produktvarianten’
   exec sp_executesql @sql

   Zum Antworten anmelden
4. AZConAdmin sagte:

   Oktober 1, 2024 um 6:34 am

   Tabellen mit mehreren Spalten suchen:

   SELECT * FROm
   INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME IN (

   SELECT DISTINCT TABLE_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
   –DATA_TYPE =’text’
   TABLE_SCHEMA in (‘Provia’)
   and COLUMN_NAME like ‘faktor’
   and TABLE_NAME not like’euBP%’
   and TABLE_NAME not like’PKH%’)

   AND TABLE_NAME IN
   (
   SELECT DISTINCT TABLE_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
   –DATA_TYPE =’text’
   TABLE_SCHEMA in (‘Provia’)
   and COLUMN_NAME like ‘LOHNARTID’
   and TABLE_NAME not like’euBP%’
   and TABLE_NAME not like’PKH%’

   )

   AND TABLE_NAME IN
   (
   SELECT DISTINCT TABLE_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
   –DATA_TYPE =’text’
   TABLE_SCHEMA in (‘Provia’)
   and COLUMN_NAME like ‘PERSNR’
   and TABLE_NAME not like’euBP%’
   and TABLE_NAME not like’PKH%’

   )

   Zum Antworten anmelden

Hinterlasse einen Kommentar