This is very simple and can be achieved using system table sys.tables.
USE YourDBName
GO
SELECT *
FROM sys.Tables
GO
This will return all the tables in the database which user have created.
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
448 Comments. Leave new
@Shivam
Script is to be executed in sql server 2005.
cannot be executed SQL Server 2000
~ IM
thanks a lot buddy
Very informative thread…
Can anybody tell me how may I get the constranits if any applied on the table’s column?
Thanks in anticipation for the help…
EXEC sp_help 'table_name'
would list out all columns with available constraints too
I am trying to select and id from sysobjects in another database in a stored procedure using SqlServer 2005.
I can not use a use databaseB statement inside of a stored
procedure and if I try to run this from databaseA
Select id from databaseB..sysobjects where …
I get a null id.
Any help is appreciated.
Thanks
Chip
hi all
is there is any sql query which gives all triggers in a database?
Try this
select * from sys.triggers
select * from sys.triggers WHERE name like ‘%dup%’
Ruwan
@Ruwan,
Just to add one more cent to your answer, That will give you list of DML Triggers. But you cannot get list of DDL triggers through that script.
Because the question was asked to find triggers in database, your solution works perfectly fine, no doubt about it.
But if we are talking about in general, DDL triggers are defined at server level but not database level, so sys.Triggers will not provide information about DDL triggers.
IM
If you want to know about DDL triggers, follow this blog post
Hi Pinal,
Can you explain me what is sql server 2005 client? As it is free but what else I required. Do I need SQL Server 2005 enterprise?
As I never worked with Sql Server, I always worked with oracle.
Please help me out
Thanks in Advance.
Rohitas Gangwar
Download SQL Server Expression edition for 2005
Hi I am Looking for a Query that will list out tables by a keyword
I use to run following Query in Oracle for teh same purpose:–
Select * from tab where tname like ‘%BANK%’
So, this query will list out all the table names with ‘BANK’
I tried to run a similar query in SQL serever 2005:–
select * from sys.TABLES WHERE NAME like ‘%BANK%’
It didn’t return any results. Also, I want my Query to search table names from all Database( I have tables with the keyword mentioned in the database)
@Chaya,
You need to understand that, there is huge difference between oracle and SQL Server naming standards,
In Oracle, we will have one database and many schemas, so if you run below script in oracle in a database, it will search this table in all schemas but only in that one database, since ORACLE has only one database per server, there will be no problem.
Select * from tab where tname like ‘%BANK%’
BUT, SQL Server has one more layer to it, In One SQL Server instance, you can have multiple databases, and in one database you can have multiple schemas.
ORACLE :
STAGE1 : SERVER
STAGE2: ONLY 1 DATABASE
STAGE3: MORE THAN ONE SCHEMA IN DATABASE
If you execute any script at serve level, because there is only one database, it can give you results.
SQL SERVER:
STAGE1 : SERVER
STAGE2: MORE THAN 1 DATABASE
STAGE3: MORE THAN ONE SCHEMA IN EACH DATABASE
If you execute any script at server level, it sees many databases, and if you do not specify the database name, by default it will search in masterdb, which is not the database you want to search in,
So in SQL Server you first need to connect to the right database and then execute your sql script,
use database_name
select * from sys.TABLES WHERE NAME like ‘%BANK%’
~ IM
Hey, thanks Imrqan you resolved my doubts.. Henceforth I will make sure that I write querirs as per the database connected to.
Hi Pinal,
Sometime I need to find some text in the whole database to detect the table and the field which contains this kind of information.
So I have an idea to create a SELECT from all tables, and then unload it in text file. I decided to do it in XML format:
So I open cursor that passes all tables and does SELECT from them.
declare @tableName varchar(100)
DECLARE tables_cursor CURSOR FOR
SELECT name
FROM sys.Tables
OPEN tables_cursor
FETCH NEXT FROM tables_cursor
INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (‘SELECT * FROM ‘ + @tableName + ‘ FOR XML AUTO’)
FETCH NEXT FROM tables_cursor
INTO @tableName
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
It gets all-tables content as many selects, so I can’t mark it and copy-paste in txt file to search in.
How can I get this output in file or in console ?
Or, may be there is another solution of my problem?
I want to know how to list all my user on my SQL 2005 database ? Can someone help me out ?
Thanks in advance
@Mark,
If you are using SQL Server 2000, you can use master..syslogins. For SQL Server 2005, you can use sys.syslogins.
Thank you~!
I can make my Table list… so easy..
How do I find out the server name. I have the database name with DB_Name(). There’s got to be something out there, I can’t find it.
SELECT @@SERVERNAME
Query or stored procedure to List of all the databases to which user has access in sql server 2005.
Hi,
one of the previous command has the statement like
if we run this bellow query it displays the list of tables in the database
SELECT * FROM TAB
It does not works in SQL SERVER 2005.
select * from sys.tables
only used to display the list of tables in the database.
With Regards,
Dhinesh
Is there a way to list all tables in the SERVER (not just in the current database)?
I am in @Database1 and need to know if a certain table/column exists in @Database2.
While in a SPROC in @Database1, how do I return a bit to tell me if a certain table exists in the other database?
if exists(select * from Database2..information_schema.tables
where table_name='your_table')
select ‘table exists on other database’
else
select ‘table doesn’t exist on other database’
hi ,
please tell me how to genrate each IDs privileges
regards
Zee