Toad World® Forums

SQL Server --> How to Find user tables from sp_tables command - excluding sys and info_schema tables.

– Hank Freeman – email and learn trick of the day

– The sp_tables command in SQL server 2008 R2 is all encompassing in that it
returns all tables in all the

– schemas or TABLE_OWNER info… So if you want a list that excludes sys and
INFORMATION_SCHEMA. use the following script…

– Enjoy !!! Hank Freeman Senior SQL Server DBA/Architect hfreeman@msn.com

– sp_tables

---- Script to find USER tables in dbo and other schemas only.

–begin

Declare

@Tables sysname

, @var_DBName sysname

CREATE TABLE [dbo].[#tables_temp]

(

TABLE_QUALIFIER VARCHAR( 100 ),

TABLE_OWNER VARCHAR( 100 ),

TABLE_NAME VARCHAR( 100 ),

TABLE_TYPE VARCHAR( 10 ),

REMARKS VARCHAR( 2 )

) ON [PRIMARY] ;

set @var_DBName = db_name () – will use the databasename

SET @Tables = ‘use [’ + @var_DBName + ']exec sp_tables ’

Insert #tables_temp(TABLE_QUALIFIER,TABLE_OWNER ,TABLE_NAME,TABLE_TYPE,REMARKS)
Exec ( @Tables ) ;

– Now find what you want

select table_owner, count (table_owner) as cnt_table_owner FROM #tables_temp
group by table_owner;

SELECT * FROM #tables_temp where TABLE_OWNER != ‘sys’ and TABLE_OWNER !=
‘INFORMATION_SCHEMA’ ;

DROP TABLE [dbo].[#tables_temp] ;

— end

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

Hank.Freeman50 (Skype)

image001.gif

image002.jpeg