0

I want to select all tables in my database

Using Dynamic-SQL:

DECLARE @sql nvarchar(max) = N'',
  @base nvarchar(max) = N'select * from $x$;';
SELECT  @sql += REPLACE(@base, N'$x$;', QUOTENAME(name)+';
')
  FROM sys.all_objects where type='U'
--PRINT @sql;
/*
select * from [trace_xe_action_map]; -- Why get this table!
select * from [trace_xe_event_map];  -- and this too!
select * from [errorTable];
select * from [table_objects];
select * from [table_fields];
select * from [alter_table];
select * from [computed_colonnes];
select * from [colonnes];
select * from [nullish_columns];
select * from [test];
*/
EXEC sys.sp_executesql @sql;

When I've run this: it tells me error that:

Msg 208, Level 16, State 1, Line 1 Invalid object name 'trace_xe_action_map'.

besides that I've added in the clause where condition and (name!='trace_xe_action_map' or name!='trace_xe_event_map'); but the same error? How can I skip those two selected values (trace_xe_action_map, trace_xe_event_map)? I also tried Top 8 with order by 1 desc to avoid two first records, But in the result I just got the first table selected dynamically! Instead of all tables.

Dale K
  • 25,246
  • 15
  • 42
  • 71
XMehdi01
  • 5,538
  • 2
  • 10
  • 34
  • 4
    Use `sys.objects` instead of `sys.all_objects`. Better yet, use `sys.tables` with no `WHERE` clause. – Dan Guzman Mar 05 '22 at 21:52
  • thanks for specification, but just out of curiosity how can I avoid the two rows returned in table? – XMehdi01 Mar 05 '22 at 21:53
  • Add `and is_ms_shipped = 0` – Stu Mar 05 '22 at 21:54
  • What do you mean avoid the two rows? – Thom A Mar 05 '22 at 21:54
  • @Larnu even though the query returned tables that I don't want how can I don't show them I've used `where clause` it's not worked for me, so I tried `top with order by` – XMehdi01 Mar 05 '22 at 21:57
  • 2
    FYI self referencing variables like that are a [documented Antipattern](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15#c-antipattern-use-of-recursive-variable-assignment) use actual string aggregation. – Thom A Mar 05 '22 at 21:57

1 Answers1

0

I've just solved with the help of dan-guzman and larnu

DECLARE @sql nvarchar(max) = N''
select @sql = String_Agg(Concat('select * from ', QuoteName(name)), '; ' + Char(13)) from sys.tables
EXEC sys.sp_executesql @sql;
XMehdi01
  • 5,538
  • 2
  • 10
  • 34