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
I WANT TO SEE ONLY USER CREATED DATABASE NAMES.HOW CAN I DO .PLEASE HELP
select * from information_schema.schemata
where schema_name=’your_schema’
how to know memory of each table and database…?
What did you mean by a memeory?
Hi Pinal ,
I am very thankful and I am appreciate your effort by helping us.
I have this case; I need to modify certain data in a certain column for all tables contain that value, for example let’s say that we have DB named “DB_Name” with the following tables:
Table “X” & Table “Y”, each one of these tables has a column name “id” where it’s a primary key in table “X” and foreign key in tables “Y”.
I f I tried to change the data in the table “Y” I get this message [The update statement conflicted with the REFERENCE constrains “FK_Y_ REFERENCE_X” the conflict occurred in database “DB_Name”, table “dbo.X”]
So I go to the table “X” and trying to change the data but I get the same message, what I need is to break the relation in between these tables to make the changes then restore the relation again.
Note: I have almost 350 tables in the DB that I work with and they are all related to each other.
Many thanks in advance :)
how to find unused stored procedures?
Hi guys,
I created a view in sql2008 and when I try to select data using the view I got an error saying “Invalid object name” in my select statement for the fields. I used dbo as the schema name in defining the view.
Any help will be highly appreciated.
Thanks,
Tony
When querying the view, use dbo as the schema name
Hi
We need to list all views from each databases in sql server. Can you help me this out?
Thanks
One option is
EXEC sp_msforeachdb ‘select table_name,”?” as db_name from [?].information_schema.tables where table_type=”VIEW”’
But you can simulate the same by following this blog post
Hi evryone,
I am trying to create an advance search query from multiple optional parameters. Could anyone tell me the best way to do this. I have 8 parameters to work with.
Thanks,
Try this logic
where
(col1=@param1 or @param1 is null)
and
(col2=@param2 or @param2 is null)
.
.
.
Thanks very much Madhivanan!!
I started out with this approach but I find myself doing a lot if else statement that end up not given me the correct results. I have to come up with every possible combination for the 8 parameters. Is there a more efficient way to come up with every possible combination that the user can search by,.
Thanks!
Tony
You need to apply the logic that I posted
It does what exactly you want with minimum code
Thanks Madhivanan for your assistance, I will apply the logic…
Thanks for the info !
HI
I need to check the my SQL server version ( 2000 or 2005)how to check the SQL server version and I need the document SQL server upgarde 2008 64 bit.can you send me document .
To know the versions, run this
SELECT @@VERSION
How I can truncate all tables except the master tables..
Do You have any script where I just pass the master tables tuncate all tables except passed (master tables) ?
Thanks & Regards
Raman verma
Follow the script showed in this blog. You can include master tables in the WHERE caluse so that they wont be truncated
Hi, I need to retrieve my database tables so that I can copy them into word document for my project submission. Im using SQL SERVER 2005.
Do you want copy the table names or structure of the tables?
If tables names
Select table_name from information_schema.tables
It structures, make use of [b]Generate Script option[/b] from Management studio
How to get list of connected users to particular DB
Good evening sir,
i have one table Users and the fields are
UserID Bigint,
UserName nvarchar(50),
SponsorID Bigint
this question is related to Level tree in which i have to find out the parent of any user recursively.
in this scenario when a new member is registered, he must have to enter his SponsorID (who is sponsoring the User). after that i have to find out the sponsors of all parent till the root node comes.
the UserID may be a SponsorID for other user.
so how can i recursively find the the userID and SponsorID of all the users with the help of Cursor.
please sir help me out from this.
thanking you,
Read about Common Table Expression in SQL Server help file
Hi,
How to list of users connected to a particular database in sqlserver 2005?
koteswarrao
run sp_who
Hey Pinal,
I am trying to get a list of all the database names along with their tables for SQL 2000, SQL 2005 and SQl 2008.
sysobjects only gives the table names
while information_schema.tables does not work in SQL 2000 Enterprise Edition.
Please help.
Lisa
Here is a quick way to do it
exec sp_msforeachdb ‘select ”?”,* from information_schema.tables’
But I recommend to follow this post
Hi,
how can i delete the logs of the database. because my database logs size is 54 GB so not being srink.
now what should i do??
Answered my question right away, thanks!
How do i sources for information from more than two tables. i mean combining more than two tables
Hint
select columns from
(
select columns from table1
union all
select columns from table2
) as t
where