Get a list of main data tables (no ref tables, no sys tables)

main_data_tables.sql

SELECT 
	 table_schema 
	,table_name 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_type = 'BASE TABLE' 
AND table_name NOT IN ('dtproperties', 'sysdiagrams')

AND table_name LIKE 'T\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_BO\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_COR\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_DMS\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_Import\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_Export\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_EXPL\_%' ESCAPE '\'

AND table_name NOT LIKE 'T\_FMS\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_LOG\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_RPT\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_Slick%' ESCAPE '\'
AND table_name NOT LIKE 'T\_SYS\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_ZO\_SYS\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_OV\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_ZO\_OV\_Ref\_%' ESCAPE '\'

AND table_name NOT LIKE 'T\_ZO\_AP\_%\_DWG' ESCAPE '\'
AND table_name NOT LIKE 'T\_ZO\_AP\_%\_Flaeche' ESCAPE '\'

AND table_name NOT LIKE 'T\_POM\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_VAR\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_OR\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_UM\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_REM\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_ZO\_REM\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_MM\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_ZO\_MM\_%' ESCAPE '\'


AND table_name NOT IN 
(
	 'T_AP_DWGSAVE', 'T_AP_DWGSAVE_XREF', 'T_AP_LinkDWG', 'T_AP_LinkDWG_XREF', 'T_ZO_AP_MarkDWG' 
	,'T_AP_Legenden', 'T_AP_Legende_Sort', 'T_Image' 
	,'T_AP_Ref_Mandant_Logo','T_AP_Ref_DIN277', 'T_VWS_PortalTree', 'ELMAH_Error' 
	,'T_Admin', 'T_Benutzergruppen_Portal','T_Benutzer', 'T_Benutzergruppen','T_Benutzer_Benutzergruppen', 'T_Benutzerrechte' 
	,'T_ZO_Benutzer_Mandant','T_ZO_Benutzer_Schedule', 'T_ZO_AP_Raum_OBJT_Kategorie' 
) 


AND table_name NOT LIKE 'T\_AP\_%\_History' ESCAPE '\'
AND table_name NOT LIKE 'T\_AV\_%\_History' ESCAPE '\'
AND table_name NOT LIKE 'T\_MM\_%\_History' ESCAPE '\'
AND table_name NOT LIKE 'T\_VM\_%\_History' ESCAPE '\'

AND table_name NOT LIKE 'T\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_AP\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_ALV\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_AV\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_TM\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_UPS\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_UM\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_UW\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_VM\_Ref\_%' ESCAPE '\'
AND table_name NOT LIKE 'T\_AP\_Kunst\_Ref\_%' ESCAPE '\'


--AND table_name LIKE '%SYS%'

ORDER BY table_name