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
448 Comments. Leave new
thanks Imran
Its working…
How could I find the structure of all tables in a DB in one query ?Is it possible?..
Advance thanks for the help…. :)
Hello,
were you able to find answer for this?
i am also looking at solution to find structure of all tables at one streach.
Regards,
Jayasheela
hello sir
i want retrieve table names of a particular selected database
please help me
first i will list the databases present in my server
then i will select one of the databases
now i want to retrieve table names of a selected database
i hope my question is clear
Advance in thanks
Hi Rashmi,
Check below query,
SELECT SysObjects.[Name] as TableName, SysColumns.[Name] as ColumnName,
SysTypes.[Name] As DataType,SysColumns.[Length] As Length FROM SysObjects INNER JOIN SysColumns
ON SysObjects.[Id] = SysColumns.[Id] INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = ‘U’ ORDER BY SysObjects.[Name]
Thanks. It was a great help.
I see the previous admin created additional database files on different drives and used this command to ge the name of each file and it’s location.
How do I find out what tables are being stored in each of these files?
Sorry, this is the command I used to see the file names and locations: select * from sys.database_files
hi,
i want to make a CSV of each tables separate.
What is sql query for this? i need to run in query analyzer.
i made a connection bet’n MS-SQL and php for this, and this php file runs every day.
Thanks
-Hitesh
What did you mean by CSV of tables?
how to list of tables/ views in the databases that have a direct ODBC connection
Can any one help me
Well prepared and answered were very useful to all of our team members. Thanks for your timely work and helpful
I want queries about sql server 2005
What did you mean by queries?
Do you want to know if the version is 2005?
If yes,
SELECT @@VERSION
Hello Friends i need a query
Table
id , Name , Age , Salary, Add1,Add2,Add3,……
This table is generated by a tool, so i dontknow exact coloume except few ,
so i want to retrive all value for those coolume which is specified in query.
Plz help or communicate if not getting the exact problem
Hi, i m new in database. Actually i want to know how can i get tables from the database using querry.
Is there any inbuilt querry or we have to make it..
please reply soon…..
Hi Vipin,
if you are using SQL 2005, then you can use:
select * from sys.objects
WHERE Type=’U’
Thanks,
Tejas
Hi,
——————————————
DECLARE TABLE_NAMES_CURSOR CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE LIKE ‘BASE TABLE’
DECLARE @ONE_TABLE_NAME VARCHAR(250)
OPEN TABLE_NAMES_CURSOR
FETCH NEXT FROM TABLE_NAMES_CURSOR INTO @ONE_TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ONE_TABLE_NAME
DELETE FROM @ONE_TABLE_NAME
FETCH NEXT FROM TABLE_NAMES_CURSOR INTO @ONE_TABLE_NAME
END
CLOSE TABLE_NAMES_CURSOR
DEALLOCATE TABLE_NAMES_CURSOR
————————————————-
the above code using in sql server2005
in in this @ONE_TABLE_NAME i’ll be storing table name,
when i print this i can able to print but the same thing when i use for deleting rows from table i’m getting error
DELETE FROM @ONE_TABLE_NAME
please help me
thanx
ambu
@ Ambanna,
You cannot give table name as variable for delete statement, select statement …
But you can store that whole statement into a variable and execute that variable which will execute whole statement.
some thing like this,
declare @sql varchar(1000)
set @sql = ‘DELETE FROM ‘+@ONE_TABLE_NAME
Exec (@Sql)
Place above three lines in your cursor, it should work.
Suggestion:
Instead of going through all this pain of cursor, why don’t you take a look at sp_MSForEachTable. Very easy to handle and less complex. Just one line code.
Link :
It has good explanation.
Regards
~ IM
Thanks for replying, it will great help to me..
Thanks Again.
Thank you very much Imran
U’ve save a lot of my time
thanx once again
Sir,
Actually i m doing a program in which database operations are performed from user interface using sql dmo.
when i m creating database it works,but when i want to delete database it gives some error…
so plz provide me the code for deleting database from user interface.
please dont give solution through sql querry….
please reply soon…….
I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.Tables’.