Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Follow @pinaldave
Send +Pinal Dave
an email at pinal@sqlauthority.com
How do I list all tables on a server?
select * from sys.tables
(or)
–list of all user defined tables
select * from sysobjects where xtype=’u’
sp_msforeachdb @command1=’USE ?;SELECT * FROM sys.Tables’
sp_msforeachdb @command1=’USE ?;SELECT * FROM sys.Tables’
For the above query i am getting this message. Kindly help me
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘’’.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘’’.
Use a single quote
To remove this error you have to do just simple thing. Just do that I say and run it..
‘use’: firstly remove this single ‘ ‘ and then add with yourself to this ‘ ‘; Because when you copy this and try to run this error will occur.
select * from sys.tables
error: Invalid object name ‘sys.tables’.
i use 2000 sql serv.
sir use to select * from employee,salary
this is the right query
will u plztell me the queury by which it onlys shows yser defined database
select * from sys.databases where database_id>4
in your connection string set your Initial Catalog to your selected database and then set your command string to: select * from sys.tables
Mr. happy select * from …. is right query. But it will show all query table combinly
Correct query for this syntex in 2000 sql server
select * from sysobjects where xtype=’u’
if u have any doubts pls contact me
Ok this works but i dont want to get it from just the test table I want to get it from all the tables that I create
“USE mrpoteat SELECT name, phone FROM mrpoteat.dbo.test where name = name and phone = phone”
How to identify the ipaddress and user who has created the table
How do I list all databases on a server?
use master
select * from sysdatabases
or
EXEC sp_databases
Hey guys, I know that Pinal is pretty busy, so I thought I would help you out with your questions.
To get all the databases on a server, you can use the same technique, but just a bit different query.
USE Master
GO
SELECT *
FROM sys.Databases
GO
For a list of all tables on a server, I would use the sp_msforeachdb procedure like this.
sp_msforeachdb @command1=’USE ?;SELECT * FROM sys.Tables’
You can see some more examples of sp_msforeachdb on my own blog, here is a link to the article.
http://www.sqlstop.com/index.php/2007/07/11/what-bol-doesnt-know-wont-hurt-you/
Sp_helpdb is used to List All Databases
Thanks Shawn,
Due to amount of emails and comment I receive I am not able to keep up with all of them. However, eventually I am able to answer them.
Regards,
Pinal Dave (SQLAuthority.com)
i can’t do it;
====================================
USE Master
GO
SELECT *
FROM sys.Databases
GO
error: Invalid object name ‘sys.Databases’.
====================================
select * from sys.tables
error: Invalid object name ’sys.tables’.
=====================================
i use 2000 sql serv.
SELECT *
FROM sysDatabases
Hi.. Mr.Pinaldave,
I have one great doubt , i need to search data in a multiple table . the table name’s are like TR42012,TR52012,TR62012 ETC… I give an Input “month” only, that input will check all the table like (TR42012,TR52012,TR62012 ETC…) and give the particular “month” value only….
its possible or not… please give me a solution to send my mail Id [email removed]
Regards,
Vinosh
[email removed]
SELECT *
FROM information_schema.Tables
This will display the list of table in the database
thnks for query on sqlauthority
I Think it is not working in SQL SERVER 2005.
Hello Ram,
Its working. INFORMATION_SCHEMA views are SQL-92 ANSI standard views and will work in future versions also. This view returns only the user tables on which you have SELECT permission. Let us know the issue you are facing.
Regards,
Pinal Dave
you can use following to show all dbases
“sp_helpdb”
thanks
i got it
but
select * from sys.tables
error: Invalid object name ’sys.tables’.
i use 2000 sql serv.
In SQL Server 2000, use
select * from sysobjects
where xtype=’u’
order by name
thank you sir i got it but i also need for show all database in particullar website and this add to dropdown listbox
Try this ………
Exec sp_MSforeachdb ‘Select * From ?..sysobjects where xtype= ”U”’
Sorry for viewing all db,
here is the query ….
select * from sysdatabases (use it in master db)
for viewing all tables in all db … use the above…
Hi
Thank’ sir
THANK YOU RADHA FOR THIS QUERY IF IT IS POSSIBLE TO VIEW LINKSERVER TABLES?
MAYUR
How could i get all the data from all the table of the specific database..?
plz reply on my mail..if possible.
thanks in advance
Bhuwanesh..
I want to view all the table in a database
USE
SELECT *
FROM information_schema.Tables where TABLE_TYPE=’BASE TABLE’
SHOW TABLES FROM (“DATABASE NAME” add your database name here)
Note that this site is for MS SQL Server and not for mysql
hi,
i want to see all the table names from a sigle database in sql server 2000. please anyone can help me!!!!!!!!!!!!!!!!!!!
Have you tried this?
select * from sysobjects
where name like ‘sys%’
order by name
SYS. naming convention will only work with SQL 2005 and SQL 2008.
For sql 2000 you need to filter the objects system table
for example if you want to see all user sepcific tables
select * from sysobjects where type=’U’
You can check BOL for SQL 2000 to identify various types.
i want to see the in second highest salary in a table sql server 2005 data base
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’
Thank you
how can i know that any changes is occur in my table structure. is there any field in any system table that value is change if any changes is done in table structure???????
please give me reply……..
thanks…………..
You can audit only thru a DDL trigger from version 2005 onwards
i can not see systables in my database please tell me how i can see and show systables
Have you tried this?
select * from sysobjects
where name like ’sys%’
order by name
I want to view all the table in a database
hi,
how to take the data to all back up in .sql with data also,
example my sql data base so use export command like it show the sql format also in the same i need the mssqlserver 2005 database back up to all data with quires
How do i list all database triggers only in an instance?
Hi,
Which query i used to list the tables in Particular Database?
select * from sysobjects
where xtype=’tr’
order by name
Mani,
Please read the post on top.
Regards,
Pinal
Thank U……..
Regards,
Mani……..
i want to see all tables in sqlserver2000;
i want to see all tables in sqlserver2000; given queries are not working im my system;
What was the error you got?
Thank you…very much…its very helpful …
Thankyou & Regards,
Princes
Works like a dream!! Thank You
Thanks for ur help in viewing all tables from a database. Can u tell me how to view the table structure ?..
EXEC sp_help ‘table name’
Thanks for your help but i am looking something like where i can display the list of database . Please anybody help me to listing of database in sql 2005.
sp_helpdb
USE THIS CODE AND YOU WILL FIND ALL THE DATABASES.
Hi,
Thanks this helped me.
can anyone tell me how to see only the user defined tables or
stored procedures or views or functions in a given database ?
1) select distinct type from sysobjects.
2) using for user table use
select distinct type from sysobjects type=’u’
for procedures type=’p’ etc….
Thanks
Thanks a lot!!!!!!!!!!!!!!
dear sir,
i want to know how to see the structure of table……..
In oracle desc tablename means it will show the structure of that particular table but the sql server it is not show ………
i need solution for this question as early as possible …..
advance thanks ………. please consider as a urgent…..
Just drag the table into query pane of SSMS and select the table and click Alt+F1 and it will displays the structure of the table.
—Bobbili Venkateswara Rao
Also refer this post to know various methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/28/different-ways-to-know-structure-of-a-table.aspx
Suresh,
to view the structure of the table in MS SQL Server
try this
Solution: sp_help tableName
Thanks,
Venky
and how you can see the structure of all tables of a specific database?
thanks in advance for your reply
I want to see all the data in all the tables in a database using the least code possible !!! Please let me know ASAP…
Why do you want to do this?
That doesn’t make any sense
how many user using my db
(same user) ?
You can run
EXEC sp_who
to know it
Thanks!
thaink you
what about
only user tables?
select * from information_schema.tables
where table_type='BASE TABLE'
I got much information regarding the database list of objects
Thank you
Harinath
I am not getting abt master table.
Thank you. Helps a lot :)
How could i get all the data from all the table of the specific database?
Thanks a lot in advance!
This will generate the query for you
select ‘select * from ‘+table_name from information_schema.tables
where table_type=’BASE TABLE’
Querry to fetch all tables in a database:
select * from sysobjects where type=’u’
————————————————–
Querry to fetch all procedures in a database:
select * from sysobjects where type=’p’
Regards
Rap…
to view all tables……
SELECT * FROM TAB;
this will work…….
This will only work in ORACLE. But this site is specific to MS SQL Server
This will work in sql or oracle not in sql server.
Dear sir,
How do i get all databases in sql server
Regard’s
pushparaj
EXEC sp_databases
Dear Friends,
I want to know how to get all table name from particular data base in sql server 2005.
Thanks.
u can get all the tables by using the comman
select * from sys.objects where type=’u’ (in sql server)
and
select * from tab (in oracle)
Hi ,
I am creating an application where I have to list all the servers and on selecting any server the combobox should dynamically have all the databases that are present on that server so that the user can select any one of them. Can anyone please help. its urgent!!!
thanks,
Gaurav
You can query the view sys.servers
@Kaushal
I want to know how to get all table name from particular data base in sql server 2005.
select * from sysobjects where xtype=’U’
if i want to check the primary and foreign key relations of particular table is it possible….if there is any solution plz tell me……..
thx in advance
Regards,
Rekha…
Primary key informations
EXEC sp_pkeys ‘table name’
Foreign key informations
EXEC sp_fkeys ‘table name’
I need to display only user defined tables from the current database.
Hi Tonny,
U will get the user defined tables using any query below.
select * from sys.tables
select * from INFORMATION_SCHEMA.TABLES
select * from sys.objects WHERE type =’U’
Hi everyone, I am new here.
I am trying to get a list of all of the tables in a DB and the size/# of rows in each table.
Does anyone know how to get this data?
Thanks in Advance for your help!
dear sir,
i want to diplay all databases name in sql2000.
@ahila
use master
Select name from sysdatabases
hope this helps,
Imran
how i can see all tables in sqlserver2000;
thanks..
One method is
EXEC sp_tables
/*
USE YourDBName
GO
SELECT *
FROM sys.Tables
*/
i used the above code but shows error stating
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.tables’.
is it dude to any syntax error
???
This will only work from version 2005 onwards. If you use version prior to 2005, use systables
USE mrpoteat SELECT name FROM sys.Tables where name = name and phone = phone
I get error
Invalid column name ‘phone’.
Invalid column name ‘phone’.
Invalid column name ‘phone’.
Im using SQL Server 05
What do you want to get as the output?
Try this
USE mrpoteat SELECT name FROM sys.Tables where name = ‘name’
@dumdum
Script1 :This will list all the system tables and userdefined tables in the database. ( meaning all the tables in the database)
use master
create table #temp1 (id int identity , name)
insert
SELECT name FROM sysobjects where type = ‘S’ or type = ‘U’
Script2: If you want to list all the system and user defined tables in all the database at a time, then you can use this script,
I am sure there must be a another good way of doing this,
use master
create table #temp1 (id int identity , name varchar(128))
go
create table #temp2 ( table_name varchar(128), db_name varchar(128) )
go
insert into #temp1
SELECT name FROM sysdatabases
go
declare @var int
declare @db_name varchar(128)
declare @cmd varchar(1280)
set @var = 1
while @var <= (select count (1) from #temp1)
begin
select @db_name = name from #temp1 where id = @var
set @cmd = ‘SELECT name , db_name = ‘+””+@db_name+””+’ FROM ‘+ @db_name+’..sysobjects where type = ”s”or type = ”U”’
insert into #temp2 exec (@cmd)
set @var = @var+1
end
select table_name As ‘Table Name’ , db_name As ‘Database Name’ from #temp2 where table_name not like ‘#temp1%’ and table_name not like ‘#temp2%’
drop table #temp1
drop table #temp2
go
Hope this helps,
Thanks,
@martin joseph
You can execute that query in Sql Server2005, it will give you all userdefined tables. Your query is absolutely correct.
In Sql Server 2000 I dont think we have an object by name sys.tables that is why we canno use that query and that is why it is returning errors.
There is something similar if you might want to use it, its sp_tables, it will list all the tables and views along with some good information in the particular database.
Hope this helps,
Imran.
Can I retrieve all tables from all database in SQL Server?
If yes please give me the query.
select * from sysobjects will give you all the tables in the database.
Note that you need to apply the fileter where xtype=’u’ for tables. Otherwise all objects will be listed
Can I retrieve all tables from all database in SQL Server?
If yes please give me the query
Can anyone please tell me how to get all the tables which has same column name in a database
select table_name from information_schema.columns
where column_name='your_col'
thanx imran for ur reply…
it was very helpful … i might have wasted a lot of time
using the code in ” sql server 2000″
Hi friends,
these are also some use full
please check
Querry to fetch all user tables in a database:
use yourdbname
select * from sysobjects where type=’s’ order by name
Querry to fetch all system tables in a database:
select * from sysobjects where type=’U’ order by name
Querry to fetch all procedures in a database:
select * from sysobjects where type=’p’
Querry to fetch all functions(userdefined or system) in a database:
select * from sysobjects where type=’fn’
Cheers,
Venkat
Hi friends,
This is very use full for finding the size, table, find …
query:
exec sp_spaceused [tablename]
result:
name rows reserved data index_size unused
tablename 145 888 KB 248 KB 16 KB 624 KB
Cheers,
venkat919
Hi Friends,
After i did the exercise
to get the all user tables from database:
— this will gives the one extra table it is “dtproperties”
1. select * from sysobjects where type=’U’ order by name
— this will gives the exact user tables from database
2.
SELECT * FROM sysobjects
WHERE
type=’U’
and
name != ‘dtproperties’
ORDER BY [name]
Cheers,
Venkat
Dear Friends
How To Find Particular Table stay Which Database ?
I want to build an if statement that will create a table with different information in it based on the database that is currently in USE (the database name in the USE statement). Different databases USE the same table but have different information in them.
Hi Pinal,
i want to fetch all databases and tables from sql server database, mysql database, oracle database and ms-access database. can u suggest me how to do this. if possible post an example for each. i want to bind all those details to a treeview in asp.net.
Thanks in Advance,
Santosh.
Does anyone have any thoughts on how to accomplish the following? I would like to query a SQL2005 db and return the table and column that contains a particular value. Based on what I have read from this site – this seems to be a good start. SELECT * FROM information_schema.Tables
I would like to add syntax that will find a varchar value and return the table, column, and value itself.
Thanks for the help.
Thanks !
I can get a list of all stored procedures that I’m using in my Db via this query:
SELECT * FROM SYS.PROCEDURES WHERE [Name] LIKE ‘usp%’ ORDER BY [Name];
(I’m looking for the name to be like ‘usp’ because that’s the prefix to all of the stored procedures I’ve created.)
What I’m looking for is where the text of the stored procedure is located. For example, I need to drop the use of a table that is causing performance issues (they’re using labor intensive triggers) and want to search the text of all stored procedures I’ve created for the table name so I know which stored procedures need changing. There are over 130 stored procedures and looking through every one of them seems too time consuming.
It would be nice to run a query that would do the search for me…like this
SELECT * FROM SYS.SomeSystemTable s INNER JOIN SYS.PROCEDURES p ON s.object_id = p.object_id WHERE [Name] LIKE tblBadTableName%’ .
Any Ideas?
I aplogize, Pinal, I found the answer within your web site.
http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/
Hi,
Could you please let me know how to retrieve the deleted table list in SQL.
Thanks,
Suba
There is no direct way until you have a copy of backup taken before tables were deleted
Hi,
How can i know that how many times my tables,views and/or functions have been executed or used by the users?Can anybody help me or can give me some idea ,plz?
Hi,
Is it possible ???
how to select data when you dont know the table name, only thing I know is database where the column resides
like I have to find how many CID columns are in my database and in which table they are resides???
Please do help me … because I have to work on Large number of database tables ….
Hi,
I want to list of my database tables’ column name with their Datatype.
Can you tell me how to do that?
Regards,
Pooja
I use this to get all Table and Sprocs on all Database on a server:
sp_msforeachdb @command1=”USE ?;select name, type from sys.objects where type in (‘U’, ‘P’) and name not like ‘dt%’ and name not like ‘#%’ order by type”
Note sp_msforeachdb has a limits the query to 128 digits.
This code shows all the tables and sprocs for each DB, the size of each table int he DB and the DB file sizes:
sp_msforeachdb @command1=”USE ?;select ‘?’,name,type from sys.objects where type in(‘U’,'P’) and name not like ‘dt%’ and name not like ‘#%’ order by 3″
, @command2=”exec sp_MSforeachtable @command1 = ‘exec sp_spaceused’”
, @command3=”exec sp_helpfile”
GO
i want default databases in sql server and their tables like EMP table in oracle . plz help me
hye,
i want to modifier a field in a database and this field is related to many database what is the code to do to modifier this field in all database at the same time
best regards
Everyone that contributed to this site has helped me tremendously. Thanks for leaving your informational note. This blog Rocks who do i give the credit to?? thanks again.
Hi pinal,
i need to write the Use statement inside the stored procedure.
but it throws error.
Is there any alternate way for this?
let me make it more clear.
i have created one stored proc in master database which takes database name as parameter.
and i want to execute the code on the respective database.
How can i achieve this?
Thanks,
Anirudha ( SQL -Developer)
Grat tips, tanks a lot !!
Good Participation Friends.
I will reply all the questions asked.
Please keep it going.
Regards,
Pinal Dave
Hi Mr.Pinal,
In SQL server 2000 i got the error message.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.Tables’.
Please help on it.
Regards,
Krishna
You need to use
select * from sysobjects
where xtype='u'
What should i do to become the future C.E.O of S.Q.L
First try to become a M.V.P. of SQL Server 2k5 / 2k8, like Pinal Dave. CEOs will consult you anyways !
@ Krishna,
In SQL Server 2000, there is no view called ‘sys.tables’. This view is only in SQL Server 2005.
You can use below quiries to get user defined tables names in SQL Server 2000.
select * from sysobjects where table = ‘u’
or
select * from information_schema.tables
Regards,
IM.
HI Imran Mohammed,
Thank you for your information…..
Regards,
Vidhya
In SQL Server 2000 this works well to return all User Tables:
SELECT Name
FROM FOTNAudit.dbo.sysobjects
WHERE xtype = ‘U’
ORDER BY Name
FOTNAudit is a specific db name. Replace with whatever you need to use.
Hello sir
How can get all the table list from Sql Server 2005.
Thanks & Regards
Jignesh Patel
@Jignesh Patel
This script will give you tables in all database ( meaning in whole SQL Server )
DECLARE @CMD VARCHAR(1000)
create table #TableNames ( DatabaseName sysname, TableName sysname)
SET @CMD = ‘USE [?]; SELECT DB_NAME()DATABASE_NAME, NAME FROM SYS.Tables’
insert into #TableNames EXEC SP_MSFOREACHDB @CMD
SELECT * FROM #TableNames — Where DatabaseName not in ( ‘master’,'msdb’, ‘tempdb’, ‘model’ )
drop table #TableNames
If you dont want system databases table names, then you need to remove — ( remove comment symbol) from above script, otherwise it will include system database table names also.
Regards,
IM.
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 : http://www.devx.com/getHelpOn/10MinuteSolution/20551
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’.
@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
http://beyondrelational.com/blogs/madhivanan/archive/2008/12/11/script-of-ddl-triggers.aspx
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
Hi Pinal,
i have 400 tables in a database. I have a requirement which need exhaustive document showing all the dependencies and information about all tables in a database(user tables). Please let me know if there is anyway to do this.
you can see all table information by
select * from sys.tables
or
select * from infomation_schema.tables
or
sp_tables
you can see all procedures by:
select * from sys.objects where type=’p’
you can see all databases by:
sp_databases
you can see all indexes by:
select * from sys.indexes
How to get tables from sqldatabase
Dear sir,
i want to know how to see the structure of all tables…..
sp_help tablename gives the output only for 1 table..i have 200 tables in my db.. should i hit the query for each table.. or is there any other solutuion for it..
plz reply…
thanx in advance.
Make use of “Generate SQL script” option from Management studio
Hi Suyog,
You can see the structure of all tables. by using….
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in(select TABLE_NAME from INFORMATION_SCHEMA.TABLES)
Excelent answer,
Thanks Sourabh Sachdeva!
Hi Sourabh,
UR answer is ohkk.. but i need INDEX constaints also in it.. so wat should i do??
plz reply..
Great man!
Hi Mani,
Please read original post as it contains answer to your question.
USE YourDBName
GO
SELECT *
FROM sys.Tables
GO
Kind Regards,
Pinal
Dear Sir,
that query is not showing me index ,constraints,columns details…
is there any other way..except sp_help tablename..
can we go for sp_msforeachtable???
can u plz provide exact query for same..
hi suyog,
If you want to see structure of all tables you can use:
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in(select TABLE_NAME from INFORMATION_SCHEMA.TABLES)
If you want to see index details then you can use:
select name,type_desc from sys.objects where type=’PK’
Hi suyog,
Not a proper way but i think u can use
declare @tableName as varchar(100)
declare @countValues as int
select @tableName=name from sys.tables
select @countValues=COUNT(*) from sys.tables
declare @startvalue as int
set @startvalue=1
while @startvalue<@countValues
begin
declare @exe as varchar(100)
set @exe='sp_help '+@tableName+''
print @exe
exec(@exe)
set @startvalue=@startvalue+1
end
after executing these statements you can see all tables structure info. If you want to combine all data into single resultset you can use temporary tables etc..
you can use this,
sp_msforeachtable ‘sp_help [?]‘
Appreciate your blog. Does anyone have any ideas on how to lookup the source table/column associated with a column in a given view? I’m looking for a way to map a column from a view (that shows up in syscolumns) back to the originating table and column that the view column is based on.
how do i know the structure of table like in mysql or oracle viz desc.i uses sp_privileges but it return the result that i dont want. i want to know the data type and its size
zee
Select * from table name
where Fname like ‘%a%’ and Fname like ‘_li’
i hope you like it
zee
Select RNo+2 As RNo,FName From DBtab
Where FName=’lia’
Thanks Sir…
Big help
select * from sysobjects where xtype=’U’
How to find the number of records from all the tables in sql 2005?
There are many methods to do this
Refer this blog post to know more about it
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx
Hi Lakshmi,
I think there is no any build in table or any built in function in sql server through you can count number of records in all the tables.
But i have created a logic through you can get number of records from all tables….
You can use this:
declare @dynamicqry as varchar(1000)
create table temp(tid int)
declare @startvalue as int
declare @endvalue as int
set @startvalue=1
select @endvalue=count(*) from INFORMATION_SCHEMA.TABLES
while @startvalue<@endvalue
begin
declare @tablename as varchar(100)
select @tablename=Table_name from (select Table_name,ROW_NUMBER() over(order by table_name asc) 'RowNum' from information_schema.tables) tt where RowNum=@startvalue
set @dynamicqry='insert temp select count(*) from '+@tablename
print @dynamicqry
exec(@dynamicqry)
set @startvalue+=1
end
select SUM(tid) from temp
Hello,
I tried to run the following query for display columnames in Ascending order from table Item:-
exec sp_columns Item order by Column_name Asc
I get the following error:-
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘order’.
How shhould I construct the query in order to get the desired results?
Hi Chaya,
you can use the following query.
select *from information_schema.columns where table_name=’ITEM’ order by column_name
Hi
I am new to this SQL.. I want to know how can get the tables from data base..
Can you tell me in a easy way please… I need it as soon as possible to resolve an issue :(
Thanks
Vidhyasri M
Hi Vidhyasri,
you can use any of this query to get all the tables from a particular database
select * from INFORMATION_SCHEMA.TABLES
or
select * from sysobjects where xtype=’U’
or
select * from sys.tables
Regards,
Ambanna Yatnal
Hi Vidhyasri,
you can use:
sp_tables
OR
you can use:
select * from information_schema.tables
OR
You can use:
select * from sys.tables
OR
you can use:
select * from sys.objects where type=’U’
Note: type=’U’ not use xtype
So all these are different way of getting all tables from sql server.
what is Invalid object name ‘sys.tables’. in sql server 2000???
can u plss help me!!!!!!!!!
use this code:
SELECT * FROM INFORMATION_SCHEMA.TABLES
reply by example
how can i display the no of tables present in the database and also displays the table name present in the database
I have Query which requires me to display ‘Active’ or ‘Inactive’ in ‘Status’ Column based on the following Condition:-
IF Todate IS Null or Todate>=Getdate() then status = ‘ACTIVE’
If Todate<Getdate() then status = 'INACTIVE'
Whereas 'Status' is not a column by itself in Agentownership table. Above condition needs to be added in the following query:-
Select AO.OwnerfirstName, AO.OwnerLastName, P.PhoneNumber, AO.FromDate
From AGENTOWNERSHIP AO
Left Join Agent A on AO.AgentID = A.AgentID
Left Join Phone P on P.PhoneiD = AO.PhoneiD
I have the following Columns in my AgentOwnership table:-
AgentOwnershipID
AgentID
OwnershipRoleID
OwnerFirstName
OwnerLastName
PhoneID
AddressID
FromDate
ToDate
OwnerDateofBirth
OwnerDriversLicense
OwnerDriversLicenseStateId
OwnerEmailAddress
Help please! I am still trying to complete the query
@dharma
Look at INFORMATION_SCHEMA.TABLES or sys.tables.
@Chaya
Just add a CASE statement to the query:
CASE
WHEN Todate < Getdate() THEN 'INACTIVE'
ELSE 'ACTIVE'
END Status
hi
In all data bases have a common table ah?
Hi.
i want a SQL querry to list the tables in a schema in DB2. can anyone help me!! Thanks friends!!
It is better you post this question at db2 forum as this site is for SQL Server
Hi,
I m converting my .net application from Mysql to Ms sql
server 2005 but the queries just don’t go well. eg.
MS sql server gives me error (SELECT * FROM Employee).
Thanks
What was the error you got?
Make sure the table exists in the current database your are working with
Hi
I’m using stored procedures to create temp tables with info and use it in SQL reporting services.
Now I have major collation issues making my life difficult.
How can I avoid collation issues in my stored procedures, or how can I change the collation of all tables and all columns in a database.
I get the following error
Msg 468, Level 16, State 9, Procedure BISSystemsOverdue, Line 103
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Hello Bertus,
Different collation of working database and tempdb could cause the collation conflit when using temp tables. To resolve this either specify the collation while creating the temp table or change the default collation of tempdb.
Regards,
Pinal Dave
its very useful info.
Thanks
this is really very explicity information
Hi,
How can I get all the databases on the sever and thier location(file name). i tried too many thing but I couldnt get it.
Thanks
Hi all,
I wanted where condition with two paramters which works in oracle but i wanted to execute in sql server 2000 which gives me error.
Kindly check the query
select * from t1 where (column1,column2) in (select column1,column2 from t2)
i require same query in sql server.If any have ideas on this plz forward to my mail id
thanks and request,
lokesh
Hi Lokesh,
Write as below:
select t1.* from t1 INNER JOIN t2
ON t1.column1 = t2.column1
AND t1.column2 = t2.column2
or another way:
select * from t1
where column1 in (select column1 from t2)
AND column2 in (select column2 from t2)
Regards,
Pinal Dave
How can I get all the databases on the sever and thier location(file name). i tried too many thing but I couldnt get it.
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
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
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:\MSSQL\Data\ActivateMBBCards_log.ldf’.
Please Provide the answer
Please give me the entair script.
Thanks & Regards ,
Sahasra
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx
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
Hi Pinal Dev,
I m using sql server2005. i run a query like…
—sp_helpdb —-which is showing all databses name with details. but i want’s to view only the –database_Name–plz help with this
Thanx
Rakesh Fhaujdar
select name from sys.databases
select name from master.sys.databases
Is there any chance to get back accidentally deleted(Using drop command)) database..?
Only if you have latest backup
Select the database from the database list,type select * from sysobjects;
it will show all of the tables that belongs to that database.
How to change values of colums from a table in SQL 2000.
You need to use UPDATE statement
Can anyone provide me SQL 2000/2005/2008 tuturial pdf.
Ranjan,
Paste this entire string on GOOGLE search page.
filetype:PDF SQL Server 2008 ebook
Regards,
Aashish
What is the relation between SQL servers and IIS.
Both are different. In what aspect do you want to know?
Hi Pinal,
How to display the tables in a particular database?
when i execute the below query it showing all the tables in the all the databases
select * from sys.tables
is there any direct query to list the tables in a particular database?
thanks
Koteswar rao
It displays tables from the current database only
select * from sys.tables
where type=’p’
how to i create the simple employee table is sql server 2005
Create table employee
(
empid varchar(10) primary key,
first_name varchar(30) not unll,
last_name varchar(30) not null,
dob datetime not null,
.
.
.)
Thanks Guys
I want to subscribe
If you want to subscribe to this site, read this blog post
http://blog.sqlauthority.com/2010/09/17/sqlauthority-news-how-to-subscribe-to-this-blog/
How to import data from oracle database to sql database.Not by genarating any text file.
It’s need to be imported through DB Link .
You can make use of import/export wizard from SQL Server
what are the concept of all database object and short note on each object
Your question is not clear. Can you give us more informations?
Hi Pinal,
I have a question about show/hide table columns in sql server based user permissions.
How can we write a query for above thing.
Please mail me or or answer my question in the post.
Thanks,
Kiran Kumar
Hi Pinal,
Is it possible to move the TEMPDB database to other drive or location ?
Read about Alter database in SQL Server help file
How can i see the all database tables in sql server 2000
how do i list the all tables in sql serve 2000
There are many mehtods
1 exec sp_tables
2 select * from sys.tables
3 select * from information_schema.tables
ya sure
How to detect and count the scanning table. I need to count query of select, insert,update, delete the table. I need to check how much has been used a table.
Can you give us more informations on what you want to do?
Hi All,
Anybody can help me how to find all db user list using script?
if u know Please send me mail.
select * from sys.sysusers
hi,
i want to clarify the difference betwwen no_truncate and with truncate_only.
pls reply..
Hi Pinal
Want to know how do I do database versioning? Actually I want to achieve something like application versioning, so when the application starts it should detect database version and compatibility with the application version. If the database version is compatible with application version then application wil continue, but if not then it should ask for the compatible database version.
Thanks
Shailesh S Bhute
You can have a table to store these informations with dates. Check for the version with the latest date
i tried so many queries to list all the tables i have created,and at the end this simple query did the magic!!
select * from Tab;
lists all the tables under the logged in user…
Note that this site is for MS SQL Server and the query you posted will only work in ORACLE
Thanx for help
use DataBaseName
Go
select * from sys.tables
Go
What is Magic Table ??how can see its data using select.
It is usually a table created inside a trigger (inserted, deleted)
thnak you very much
The best artcle at any time ! great name great explanation and the website very interesting !
Hi Pinal, Madhivanan
I have a database with 1000 tables (for each table _id is the primary key).
Would it be possible to select all tables which are having _id > 1500 (i.e Primary Key value > 1500).
Thanks in advance,
Sri.
Run this
select ‘ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables
Copy the result and run it
Thank you Madivanan.
I did the similar thing in the fist place, but I did have a thought to check if is there any elegant way to do this without need of running those queries.
SELECT * FROM SYS.TABLES
then loop through all the tables to check if the table meets the criteria.
SELECT COUNT(*) FROM [] WHERE Table_id >1500 HAVING COUNT(*) > 0
then appended all the table names to a list (tables which return a value) .
Coming to your query:
select ‘ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables
only composes the SQL like :
select * from employee where employee_id>1500
but it not tell us if the table has meet our criteria?
again I need to check if the query returned any rows etc., in program.
Please let me know if is there any better approach. Many Thanks for your reply.
Sri.
Ok. Execute this code
declare @sql varchar(max)
set @sql=”
set @sql=@sql+’ select * from ”’+table_name+”’ where _id>1500”’ from INFORMATION_SCHEMA.tables
exec(@sql)
It will list out required data from all the tables
Thanks for your reply and patience.
I am having trouble in executing the above query.
I have replaced ’ with single quote, but it is erroring with a messgae : Incorrect syntax near the keyword ‘from’.
(tablename_id is the primary key rather than just the _id)
Could you please advise?
Thanks,
Sri.
There is an extra single quote. Try this
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables
exec(@sql)
Thank you Madhivanan, this is working fine now.
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ select * from ['+table_name+'] where ['+table_name+'_id]>1500′ from INFORMATION_SCHEMA.tables where table_type=’BASE TABLE’
exec(@sql)
But I wonder if is there any way to list only the table names not the tables data (tables which are having rows with id> 1500)?
Thanks,
Sathish.
Try this code
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ if exists(select * from ['+table_name+'] where ['+table_name+'_id]>1500) select top 1 ”’+[table_name]+”” from INFORMATION_SCHEMA.tables where table_type=’BASE TABLE’
exec(@sql)
Thank you Madhivanan, This SQL is working fine.
Many Thanks for your patience.
You are welcome Sri. Thanks for the feedback
i have a problem with a step in my sql databse which is as the following:
- Create a Stored Procedure that copies the record(s) in tblDAT_MyStar into tblDAT_MyStartHistory every time the stored procedure is called. The example is given here to help you out. In your code, make sure that the value returned by GetDate() is assigned to a local variable first. That is, you need to declare a local variable of type that is suitable of storing a date together with time. Populate the header section with your info.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: ****
– Create date: 1/18/2011
– Description: Show an example of SP and copying records
– =============================================
create procedure [sp_CopyingClass]
as
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
declare @Status as int
— Insert statements for procedure here
Begin transaction
INSERT INTO [dbo].[tblDAT_ClassHistory]
([ClassCode]
,[InsertedAt])
Select Classcode, GETDATE() from tblDAT_Class
select @Status = @@ERROR
if @Status = 0
commit
else
rollback
return @Status
END
GO
the tables are:
- tblDAT_MyStar
column name
FirstName nvarchar(128)
LastName nvarchar(128)
john nchar(10)
- tblDAT_MyStartHistory
column name
ID_AA21080 int
FirstName nchar(10)
LastName nchar(10)
InsertedAt nchar(10)
please help
hello sir
i knew sql server 2005 i knew create tables and backup and maintain column and row . but i have no exp in database field. i don’t know about use of sql server in mnc companies. i dont know use full text search and stored proceudre and triggers
what should i do in database file
plz help me this query
becoz its very important for my life
happy
Have you searched for them in SQL Server help file?
Hi every one!
how can i view all the tables in my database on my admin page. i tried
SELECT * FROM INFORMATION_SCHEMA.TABLES
it gives me “Resource id #3″ and doesn’t show any thing on the page.
pliz help.
Hi All,
I want the list of all tables and views of the database which has “emailaddress” as a field.
How can i achieve this?? Please throw some light..
Regards,
Sneha
select table_name from information_schema.columns
where column_name=’emailaddress’
Hi ,
Query:
1) Any giving user id give all their business that have been in active state by admin1
============
2)List all the posts under top category Science
=========
3) All the address under country Singapore..
please reply to my mail ASAP thanks
Raj
[email removed]
What have you tried so far?
hey,
anyone can help me with this problem,
i need to count all numeric type collum of each db table
select table_name,COUNT(COLUMN_NAME) from information_schema.columns
where NUMERIC_PRECISION is not null
group by table_name
thanks for help
how i can get the list of all database on the system
use this
exec sp_databases
Can anyone suggest me any tool or agent through that
i can pull out a system hardware information means asset scanning & insert the data into a sql database.
Hello Sir,
If i want join the Sys.Databases and Sys.Tables how can i do?
Dear,
i am using ” Toad for Oracle” and i went to fetch all tabels is the database and i can’t use the below quary due to the below error.
“table or view does not exist”
please advice
Note that this site is for MS SQL Server
I need to create history tables based on their orginating tables. The only exception being I need a new primary key field and the primary key field from the originating table needs to become a regular numeric field. I know comething can be created using sql Server 2005 sys.tables, sys.columns, etc. Has anyone done this? Can you offer up some sql to help me along my way?
plz tell me the query for getting information about all views in sql server
Can some one help me in creating a new table in the database where you have only master table?
Hello Pinal,
Can you help me how to find out the usage of the tables on the databases, and its growth on the tables from period to period…
to collect the tables that are heavily used…
Regards,
Rohit
EXEC sp_spaceused ‘table_name’
Thanks for the prompt response Madhivanan….
But it gives only row count, used and unused information only, that too it happening me to do on each and evry table as there are lot many tables in each of my databases.
So is there any process to look at a time on whole database that too from time to time period, so that I can figure out the growth in the tables and which are heavily used…
For example: suppose there is 100kb of used data on a table and after two days it may be something more like 150kb… so I want that growth from time to time so that I can aware of getting the information which are heavily used…
I hope u got understood…. if not let me know, I’ll try to explain little more abt my issue…
Regards,
Rohit
Hello,
Can anyone help me quickly how to format this datepart(week,date) to two characters length… I mean if the work week is one then it has to mention as 01 not as ’1′… in the same way if the work week is 2 then it has to mention as 02 instead of 2…. and need to go on till 09….
Regards,
Rohit
Hi Pinal,
can you let me know where am doing wrong in this stored procedure….
Actually if I run this query its working fine in 4 seconds of time…
BEGIN
SET @startdate = (SELECT CONVERT(VARCHAR(10),date_begin,101) FROM ww_calendar
WHERE ww = SUBSTRING(@wwstart,5,LEN(@wwstart)) AND year = SUBSTRING(@wwstart,1,4))
SET @enddate = (SELECT CONVERT(VARCHAR(10),date_end,101) FROM ww_calendar
WHERE ww = SUBSTRING(@wwend,5,len(@wwend)) AND year = SUBSTRING(@wwend,1,4))
INSERT INTO @temp_sn
SELECT * FROM sntrax_current..SplitString(@snlist, ‘,’)
DECLARE c_sn CURSOR FOR
SELECT * FROM @temp_sn
OPEN c_sn
FETCH next FROM c_sn INTO @sn
WHILE (@@fetch_status -1)
BEGIN
SET @sn = ltrim(rtrim(@sn))
SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”’+ @startdate + ”’AND
shpm.shipdate = @startdate AND
shpm.shipdate <= @enddate AND
Substring(snm.sn, 1, 4) = @sn order by shpm.shipdate desc
Kindly help me in this query pls….
Regards,
Venkat
oops sorry….
the above sent query was working fine in 4 to 6 seconds, but if I remove the query out of @sqlquery parameter it taking much time (approximately 3 to 5mnts of time)
INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(’0′ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >= @startdate AND
shpm.shipdate <= @enddate AND
Substring(snm.sn, 1, 4) = @sn order by shpm.shipdate desc
can you help me where am doing wrong pls….
Regards,
Venkat
Still waiting some one answer to my query pls….
To list all tables from existing database use sql command “select * from tab” without double quotes.
Hello Pinal,
Expecting an answer from you about my query….
Actually why its showing such an impact on that parameter… when I run the query by initializing the query into parameter “Set @sqlquery = …” its working fine giving output in seconds… but when I take out of the parameters and run directly its taking much time… why there is such a difference in run time….???
Looking for reason from you… and help me with a correction in my query….
Regards,
Venkat
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = ‘U’
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 ASC
By Thameem
Hi Pinal Dave,
Greetings. I have a problem in creating a query. Can you please see and provide some help.
Scenerio is:
id column_name parentid
1 A 0
2 B 1
3 C 1
4 D 3
5 E 2
6 F 4
7 G 0
The columns which has parentid as 0 are the top most items and rest all have the parent-child relation.
I want it somelike
A
B
E
C
D
F
G
Where B,C are child of A, E is child of B, D is child of C an so on.
Thanks in advance….:)
I would recommend — select * table from information_schema.tables — as well. It works pretty much good.
Column ‘a’
—————————————————–
Select e.empno,d.deptname from emp e
inner join dept d on e.deptno=d.deptno
Select e.empname,ed.salary from emp e
inner join empdet ed on e.empno=ed.empno
The above are two rows in column named as ‘a’ in a table ‘t1′
Now i need the result to be as
column ‘Obj_names’
——————————————-
emp
dept
emp
empdet
(ie), I need a query that will exactly get the table names from those SQL statements and display me..
IS THERE ANY OPTIONS FOR THIS..
Help me PINALDAVE or ANYONE who can ??????????
Regards,
Arunkumar
MCTS – SQL Server 2005
I dont think there is an option to do this. Why do you want this? You can parse the statement and get the table names
Hi Pinal Dave,
Is it possible to execute a query in two database together?
For example :
select database1.Name from sys.procedures where [name] not in (select database2.Name from sys.procedures)
Okay I found the solution
Here we go:
Select t1.Name from
db1.sys.procedures t1 where t1.name not in (Select t2.Name from
db2.sys.procedures t2)
Hope it helps someone….
Pinal Dave, you are doing fabulous job man. keep going..do well….
Regards
sir i want know about how to delete same name table from multiple database .
Dear All,
I have a database with three users and I need to show only tables that related to a particular user instead of showing all tables. So how can I achieve this?
Any help must be appreciated.
Regards,
Jayaram.
Exact select query in sql,
select *from tab;
and
sql server is
select *from sys.tables
Hi All,
I want know about how to db backup via command line.
or i need DB DDL query.
It was great meeting you at PASS; once again, I searched the web for a quick answer, and yours was the first that popped up. As always, you’re a wonderful resource.
-Jennifer
Thank you so much!
I am now following you as well!
Hi dave,
i want to thank you very much for this useful blog. keep helping us !
between brackets : are one of my references.
I suggest if we add “where is_ms_shipped = 0″ will return tables that user created, because i found that only select * from sys.tables returns all tables (even tables that was created by replication or sync process etc ..)
regards
Alternatively, you can query information_Schema.tables view to access the user defined tables so that you dont need to query on system tables directly
Hi,
I connected sql server through sql developer, in that i am not able see some table while querying like “SELECT *FROM TEST” ,its giving invalid object.but if select TEST table by clicking on TEST object its showing all data and structure.Please help in this regard thanks.
Make sure you have used the correct object owner name
Hello Pinal / Madhivanan,
First up, EXEC SP_MSFOREACHDB does not work on my DB runnin SQL 2k8 R2.
Second, EXEC sp_help ” returns the schema & metadata pertaining to one table at a time. Is there a way to put this in some kind of LOOP, or iteration & execute it against all the Tables within the DB at the same time ?
Lastly, why is it so that SELECT * FROM SYS.TABLES returns an Error like Object not found, whereas, SELECT * FROM sys.tables, executes successfully ? Why is the Query parser Case-Sensitive … ??
Any suggestions ?
Regards,
Aashish Vaghela.
The database seems to be a case sensitive. What is the error you got when you triede EXEC SP_MSFOREACHDB ?
Hi, Can you post the query for SQL server to get the active objects?
how can we delete total tables which are not used from start or which doesnt have data ?
how can we delete total tables which are not used from start or which doesnt have data ?
in a single server
How to Create a stored procedure which takes ‘database name’ and table name as input ‘parameters’ and return column name list of that table.
This is subject to sql injection and use it carefully
declare @sql varchar(1000)
set @sql=’select column_name from ‘+@dbname+’.information_schema.tables where table_name=”’+@table_name+””
exec(@sql)
can we see the data of offset table
i want all table names from the database. please let me know the syntax
select * from user_objects where object_type = ‘TABLE’;
Better still –
select object_name from user_objects where object_type = ‘TABLE’;
Hello EveryOne,
I want to get the Table Values of respective Project of QC from SQL Server 2005, which Contains n number of Projects.
Can any one tell me the query for that ??
how can I get all table names with its record count in one query
Hi,
I have one SP and i want to know the dependency in all databases i.e wher the sp is using.
Sp_syscontents ‘store_proc_name’ is fine or any other sql statment?
Please reply.
Thanks
This command may help to list all columns, all tables and for all databases in SQL server.
use master
go
DECLARE @CMD VARCHAR(1000)
create table #TableNames ( DatabaseName sysname, TableName sysname, ColumnName sysname, DataType sysname, Length sysname)
SET @CMD = ‘USE [?]; SELECT DB_NAME()DATABASE_NAME, 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]‘
insert into #TableNames EXEC SP_MSFOREACHDB @CMD
SELECT * FROM #TableNames Where DatabaseName not in ( ‘master’,'msdb’, ‘tempdb’, ‘model’ )
drop table #TableNames
I’m trying to geat a list of tables in all views in a DB including the views that have their source tables in another database…
SELECT * FROM [INFORMATION_SCHEMA].VIEW_TABLE_USAGE only gives me a list of views that have their input tables sourced from the current database
this is too good……………
Anyone find a solution to this one, trying to restrict sys, and information_schema objects from ODBC and ms sql managment console on sql server 2008 r2 dbase but having no luck.
found – http://www.sqlservercentral.com/Forums/Topic671366-359-2.aspx?Update=1
but they never had a full proof answer either from 3 yrs ago. Help please as I am trying to set up the connection to the dbase where the user will only be able to see a select set of tables, views and with either userid pass word via ms sql mang console or ODBC have just select access to tables for users, keeping things locked down from the user. any ideas?
Brett Stutzman
I require all the databases whether it is attached to Server or not. Is it possible ? Help me
I have one great doubt , i need to search data in a multiple table . the table name’s are like TR42012,TR52012,TR62012 ETC… I give an Input “month” only, that input will check all the table like (TR42012,TR52012,TR62012 ETC…) and give the particular “month” value only….
its possible or not… please give me a solution to send my mail Id ::: “vinosh.john@gmail.com”
gr8 blog …. dis discusion helped me alot …. nd cleared many doubts too…
Is it possible to show the child/children of a table and show the links?
1)how to write query for comparing userid & password in sql 2005
I have got solution from this site thank’s
Hello. I am a complete newbie and your articles are some of the best that I have come across.
Hi Pinal,
I want to know one thing. it is possible that we can set the table name at SQL which can be use at front end side. I mean if I assign any name to ‘select query’ and it will return table to front end with a given name.
1) excute tis (below) procedure …, i will get 3 tables ..ok
2) and …i m using ExcuteDataSet() in frontend like DatabaseLayer.cs (.Net)
3) so tat time ….(from the Dataset) …Table1,Table2,Table3,Table4…..etc( table index 0 ,1 ,2,3 …etc)
4) I want to access Dataset.Table['tablename which is given in backend']?
not by index value
*******************************************
in Store procedure (Sample)
************************************************
CREATE PROC spname(@parm1 int,
@param varchar(10)
) AS
BEGIN
SELECT filed1,filed2,filed3 FROM table1
SELECT field1,filed2,field4 FROM another_different_table_1
SELECT field1,filed2,field4 FROM another_different_table_2
RETURN………………..
END
Hi ..
how to get all the table structure with select statement in a database?
ex: Emp table having name, department and salary columns
i want output like
select name, department, salary from emp
How will i run parallel load in SSIS package, like i have 10 text file and my system configuration is 7cpu and 32gb ram, so i want to utilise all the cpu and 32gb ram, so how can i run all the text file in one short. not like dependence . as of now my job will take one by one instead of that i have load parelley load all the data into one table.
Note: I have one table , source 10 text file,destination one table/
Please help me to understand the logic to implement and utilize all the cpu in the server
Hi experts, i need your help. I need to create a store procedure which will receive an four arrays of parameters. Each one has parameters split with a comma. I need to retrieve data very fast and i cant just use the split function for security reasons. Can give some advice. Thanks