List user tables in a database??
-
Hello! How is it possible to list all objects(tables, views) in a database that have been created by the databases designer and NOT by SQL server? I know that EXECUTE sp_databases @table_type="'TABLE'" shoudn't list any system tables. But if I execute this statement on a database I created, I always get tables I didn't create :mad: and which the Server Explorer of VS doesn't show? So how can I get a list of only the tables (it's the same for the views) that I created?? :confused: Cheers TIA Martin "Situation normal - all fu***d up" Illuminatus!
-
Hello! How is it possible to list all objects(tables, views) in a database that have been created by the databases designer and NOT by SQL server? I know that EXECUTE sp_databases @table_type="'TABLE'" shoudn't list any system tables. But if I execute this statement on a database I created, I always get tables I didn't create :mad: and which the Server Explorer of VS doesn't show? So how can I get a list of only the tables (it's the same for the views) that I created?? :confused: Cheers TIA Martin "Situation normal - all fu***d up" Illuminatus!
exec sp_tables NULL,dbo,dbname,"'TABLE'" exec sp_tables NULL,dbo,dbname,"'VIEW'" Mazy **"If I go crazy then will you still Call me Superman If I’m alive and well, will you be There holding my hand I’ll keep you by my side with My superhuman might Kryptonite"**Kryptonite-3 Doors Down
-
Hello! How is it possible to list all objects(tables, views) in a database that have been created by the databases designer and NOT by SQL server? I know that EXECUTE sp_databases @table_type="'TABLE'" shoudn't list any system tables. But if I execute this statement on a database I created, I always get tables I didn't create :mad: and which the Server Explorer of VS doesn't show? So how can I get a list of only the tables (it's the same for the views) that I created?? :confused: Cheers TIA Martin "Situation normal - all fu***d up" Illuminatus!
Martin Häsemeyer wrote: How is it possible to list all objects(tables, views) in a database that have been created by the databases designer and NOT by SQL server? to list all user tables select * from sysobjects where type='U' to list all views (may not all be user though) select * from sysobjects where type='V'
Soliant | email "The whole of science is nothing more than a refinement of everyday thinking." -Albert E.
-
exec sp_tables NULL,dbo,dbname,"'TABLE'" exec sp_tables NULL,dbo,dbname,"'VIEW'" Mazy **"If I go crazy then will you still Call me Superman If I’m alive and well, will you be There holding my hand I’ll keep you by my side with My superhuman might Kryptonite"**Kryptonite-3 Doors Down
Unfortunately that's exactly the same as exec sp_tables @table_type="'TABLE'" :( I get my user tables and a table called dtproperties... But Server Explorer can sort it out :confused: But thanks anyway :) Cheers Martin "Situation normal - all fu***d up" Illuminatus!
-
Martin Häsemeyer wrote: How is it possible to list all objects(tables, views) in a database that have been created by the databases designer and NOT by SQL server? to list all user tables select * from sysobjects where type='U' to list all views (may not all be user though) select * from sysobjects where type='V'
Soliant | email "The whole of science is nothing more than a refinement of everyday thinking." -Albert E.
This method seems to produce exactly the same results but with additional information... :( Maybe I'll have to manually exclude the additional sysobjects and hope that their names are always the same. Cheers and thanks for the reply Martin "Situation normal - all fu***d up" Illuminatus!
-
Hello! How is it possible to list all objects(tables, views) in a database that have been created by the databases designer and NOT by SQL server? I know that EXECUTE sp_databases @table_type="'TABLE'" shoudn't list any system tables. But if I execute this statement on a database I created, I always get tables I didn't create :mad: and which the Server Explorer of VS doesn't show? So how can I get a list of only the tables (it's the same for the views) that I created?? :confused: Cheers TIA Martin "Situation normal - all fu***d up" Illuminatus!
-
Hello! How is it possible to list all objects(tables, views) in a database that have been created by the databases designer and NOT by SQL server? I know that EXECUTE sp_databases @table_type="'TABLE'" shoudn't list any system tables. But if I execute this statement on a database I created, I always get tables I didn't create :mad: and which the Server Explorer of VS doesn't show? So how can I get a list of only the tables (it's the same for the views) that I created?? :confused: Cheers TIA Martin "Situation normal - all fu***d up" Illuminatus!
Try this (I'm not at a SQL box at the moment so it might not work 100%)
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND TABLE_TYPE = 'BASE TABLE'