– 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
678.414.0090 my cell Primary
Hank.Freeman50 (Skype)