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
Looks like some good information here. I’m trying to create two combo boxes. The first one would show all the SQL servers (so I wouldn’t have to program to a specific server) – so I could easily compile the program for our clients. Once a server is selected, then show only those databases that contain a specific table name (to show only those databases that apply to my program) – even the first combo box could do this as well – only showing those servers that have any database that contains a specific table name.
I’m using Visual Studio 2008 and have a dataset in the program. Is there an easy way to update that with the selections from these dropdown boxes?
Thanks in advance for any help.
Hi Mike,
Use the below script:
Use tempdb
GO
SELECT TOP 0 * INTO DatabaseFiles
FROM sys.database_files
ALTER TABLE DatabaseFiles
ADD DatabaseName varchar(255)
EXECUTE sp_msforeachdb ‘INSERT INTO DatabaseFiles SELECT *, ”[?]” FROM [?].sys.database_files’
SELECT DatabaseName,[name] as [FileName],physical_name as FilePath, * from DatabaseFiles
Regards,
Pinal Dave
Hi Pinal,
Am working on scripts to help me in daily monitoring tasks..
I want to get the database names, its file names, total size and available space..till now am able to get all the bits except for the last one
—————————————————————————
SELECT a.name as [DBname], b.name AS ‘File Name’ , case when b.type=0 then (select ‘Data’) else ‘Log’ end as [File_type] ,b.physical_name AS ‘Physical Name’, b.size/128 AS ‘Total Size in MB’,
b.size/128.0 – CAST(FILEPROPERTY(b.name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’
FROM sys.databases a left outer join sys.master_files b
on a.database_id = b.database_id
—————————————————————————
This gives me list of everything what i want on that server but regarding “available space” it can only retrieve for the current database files..I want that for all the database files on that server..
Looking forward to your reply..
Thanks
Shree
how do i select all the records in a particular database using a particular in where condition..i had a commom column Eldate in all tables……..
See if this helps. This will search for a value in all character columns and return table name, column name and searched value
It is better to use Information_schema views than the system tables
SELECT * from Information_schema.tables
WHERE table_type=’BASE TABLE’
I am looking for a script that goes a step beyond this. I need a script that will read all of the columns in a table, and output % populated (%0s for numbers or nulls for char/varchar), min/max ranges and date ranges. I used to have one, but it has been a few years, and I can’t locate it. We used to use it as a great way of validating a client’s data. We would use the client’s data to perform audits, and we had to make sure we had the right date ranges, adequately populated key fields (things like Invoice Number or Check Date). It would allow us to throw out totally null columns, or alert us to errors or invalid exports/imports (ETL process) such as importing a field as the wrong type, causing valid values to be left out on import.
I am in a situation again where I could really use such a script.
I have been searching for something like this again for several days now, to no avail. Any help along these lines would be greatly appreciated!
I am looking for a script to display all tables in a Database and each table’s collation. SQL Server 2005
select table_name,column_name,collation_name from information_schema.columns
order by table_name
Rellay Good Information for US.
Thank you and keep Going ON
Hi,
I need a SP for dynamic display of files in different databases..
Hello Keerthi,
If you mean to get data and log files of all databases then use the below statement:
sp_msforeachdb ‘select * from ?.dbo.sysfiles’
You can also insert this result into a temporary table for better readability.
Regards,
Pinal Dave
Hi,
I want to get the list of triggers in a database how to get this?can u help on this?
select * from sys.triggers
Hi vijayanj,
you may use
select * from sysobjects where type=’tr’
or
also use
select * from sys.triggers
Hi,
I’m trying to create a table from a query, but want the
Query Name to be entered as a parameter.
Is there a way to do that?
Looking forward to your reply.
Thanks
Yodit
e.g
SELECT Dept.cwid INTO LabelHold
FROM [Please Enter Query Name]
Hi Yodit,
I think you need execute it with Execute command.
1. You need to build a query with string manipulation
2. Execute it with Execute(Query)
Example:
DECLARE @qry VARCHAR(MAX)
SELECT @qry = ‘SELECT * FROM employees’
DECLARE @Qry2 VARCHAR(MAX)
SELECT @Qry2 = ‘SELECT * INTO tempdb..test FROM (‘ + @qry + ‘) x’
EXEC(@Qry2)
SELECT * FROM tempdb..test
@qry, is your parameter which contains your query.
@Qry2, is actual query which will be executed. It also contains your query from parameter.
NOTE: @qry should be surrounded by parenthesis, so your query looks like:
SELECT * INTO tempdb..test FROM (SELECT * FROM employees) x
I hope this will solve your problem.
Thanks,
Tejas
SQLYoga.com
Hi Tegas,
Thanks for your response, but I need to automate this task in Access. The database is in Microsoft Access.
I’m trying to create a query in access that will
ask me the TableName or queryName as a parameter from which to get the data. ) I don’t know VBA.
I would appreciate any help, hints or suggestions.
Many thanks!
Yodit
Hi
I need to fetch a common column from a number of tables in a database .I have the list of names of the tables I need to fetch data from by using the query:
select table_name from information_schema.tables where table_name like ‘G%’ order by table_name
Please help me.
Hello Shailja,
Do you want list of columns name that are in more than one table?
If yes get the name of such columns by below query:
select column_name from information_schema.columns
group by column_name
having count(*) > 1
And then get the name of all tables for each column.
Regards,
Pinal Dave
please can anybody send me the query for
Displaying Database from SQL and then list of tables of DYNAMICALLY selected Database?
please give attention over the word DYNAMICALLY selected……….
thanx plz mail me as soon as possible….
thanks………
Hello Tapan,
you can set hte database name in @db variable and execute the followinf batch:
declare @str varchar(1000), @db varchar(100)
set @db = ‘yourdbName’
set @str = ‘select * from ‘ + @db + ‘.information_schema.tables’
exec (@str)
Regards,
Pinal Dave
Hi Dave,
Thanks for sharing all the great info. You have saved me many hours of work.
Sorry if this is repeated somewhere but I could not get this to work:
USE
SELECT *
FROM information_schema.Tables
where TABLE_TYPE=’base table’
Then realized it is case sensitive for me because I have binary sort order. So this worked for me.
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE table_type = ‘BASE TABLE’
Thanks again
Bob
Hi,
I am new in SQL Server 2005. I want to search a customer name from the any tables exists in a same database. Could any one help to write such query by which we can search any text in database irrespective to the tables name. (i.e. I am not aware that the required details existing in which table, only database name is know)
Please help!
Thanks in advance
AKHILESH
The following post will help you.
It will search for a particular text in all tables and return table name, column name and value if found
Hello Pinal ,
I have an error on attach/detach
I detached my database, right now I dont have the database , then I am going to attach the database , buy it’s giving some error , the error is as follows..,
Error 823:I/O error 38(Reached the end of the file.) detected during read at offset 0*000002ac680000 in file ‘S:MSSQLDataActivateMBBCards_log.ldf’.
Please Provide the answer
Please give me the entair script.
Thanks & Regards ,
Sahasra