Showing posts with label along. Show all posts
Showing posts with label along. Show all posts

Monday, February 20, 2012

List all the tables on a SQL Server

How do I list all the tables on a SQL Server, along with the database that
they are in ?
Have tried the following ways, but they only list tables in the current
database:
select * from sysobjects
exec sp_tables
select * from INFORMATION_SCHEMA.tables
Thanks, CraigAm Tue, 7 Mar 2006 03:43:59 -0800 schrieb Craig HB:

> How do I list all the tables on a SQL Server, along with the database that
> they are in ?
> Have tried the following ways, but they only list tables in the current
> database:
> select * from sysobjects
> exec sp_tables
> select * from INFORMATION_SCHEMA.tables
> Thanks, Craig
maybe this can help:
http://www.dbazine.com/sql/sql-articles/larsen5
bye, Helmut|||This should get you starting:
EXEC sp_MSforeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'
sp_MSforeachdb is not documented, so you really should create your own versi
on of it, with a bit of
inspiration from the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A0DD05BA-5962-4D6E-A641-496D8E2684A8@.microsoft.com...
> How do I list all the tables on a SQL Server, along with the database that
> they are in ?
> Have tried the following ways, but they only list tables in the current
> database:
> select * from sysobjects
> exec sp_tables
> select * from INFORMATION_SCHEMA.tables
> Thanks, Craig|||Try:
create table #t
(
dbname sysname not null
, tabschema sysname not null
, tablename sysname not null
, primary key (dbname, tabschema, tablename)
)
go
exec master.dbo.sp_MSforeachdb 'insert #t select table_catalog,
table_schema, table_name from ?.information_schema.tables where table_type =
''base table'''
select * from #t
drop table #t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A0DD05BA-5962-4D6E-A641-496D8E2684A8@.microsoft.com...
How do I list all the tables on a SQL Server, along with the database that
they are in ?
Have tried the following ways, but they only list tables in the current
database:
select * from sysobjects
exec sp_tables
select * from INFORMATION_SCHEMA.tables
Thanks, Craig|||Thanks All -- that was exactly what I was after|||Run this
select 'exec '+name+'..sp_tables' from Master..sysdatabases
Copy the result back to QA and run them one by one
Madhivanan

Linux Servers(Verified)

Hey Guys - I was just wondering - if you have started to use Linux Servers along with SQL Server- and if so, which Distribution of Linux and for what purpose. Cheers - Mark Smith

I am guessing you mean do we have a driver that works apps on Linux machine to access SQL Server. Currently, we have JDBC driver which can be used on any machine with JVM installed to access SQL Server. The distribution of Linux doesn't matter as long as JVM 1.4 and later can be installed on the machine.

HTH

Linux Servers

Hey Guys - I was just wondering - if you have started to use Linux Servers along with SQL Server- and if so, which Distribution of Linux and for what purpose. Cheers - Mark Smith

Mark_Smith111 wrote:

Hey Guys - I was just wondering - if you have started to use Linux Servers along with SQL Server- and if so, which Distribution of Linux and for what purpose. Cheers - Mark Smith

I don't understand this question... What are you asking, exactly?

SSIS won't run on Linux. SSIS can connect to Linux using a variety of tasks, though some of them have issues as well (FTP Task, for instance.)

Linux Server(Verified)

Hey Guys - I was just wondering - if you have started to use Linux Servers along with SQL Server- and if so, which Distribution of Linux and for what purpose. Cheers - Mark SmithHey Guys - I was just wondering - if you have started to use Linux Servers along with SQL Server- and if so, which Distribution of Linux and for what purpose. Cheers - Mark Smith

Are you kidding me? Trying to be funny or are you just spamming the forum?

SQL Server is a Microsoft product. Have you ever heard of any Microsoft products running on Linux? No my friend, SQL Server runs on Windows only.|||Hey Guys - I was just wondering - if you have started to use Linux Servers along with SQL Server- and if so, which Distribution of Linux and for what purpose. Cheers - Mark Smith
It really depends on your definition of along with. As kaffenils said, you cannot run MSSQL on a Linux box. What you can do however is running SQL Server on its own server, and then use that server in a mixed environment. You may for instance have Apache/PHP sites that makes use of MSSQL, and if desired you may use SQL Server Integration Services to make integrations between various databases running on the Linux platform, as for instance DB2, Oracle, PostGres and MySQL.