USE DatabaseName GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp
Reference: Pinal Dave (http://www.SQLAuthority.com)
277 Comments. Leave new
Hi Frnds
You can get list of all user defined table and no of rows from your sql server database with this query.
It is quiet efficient and optimized.
SELECT Rows, object_Name(id) as TableName FROM sysindexes WHERE indid < 2 and OBJECTPROPERTY(id, ‘IsUserTable’) = 1
Cheers
abhi :)
Note that for versions prior to 2005, you need to use DBCC updateusage for correct counts
Also refer this post
Hi,
Really this is nice query. Thanks…… i am very happy
Hi,
In T SQL, how can we automate:
– to copy all the columns of a table to make a new table
– change the names of the new table and its columns using something like a prefix, which will make the difference of structures between the two tables.
Thanks
I guess u r looking something for this
Use AdventureWorks;
Select EmployeeID as ‘EmpNo’, LoginId as ‘LoginName’, BirthDate as ‘DOB’
into MyNewTableName
from HumanResources.Employee;
Go;
It creates new table and populates result set in that table
@ Bimpa ,
You can do two things, based on what you are looking at.
1. If you just want a new table structure with out any constraints, keys, indexes…. just the columns and the table thats it, then try using…
select * into new_table_name from old_table_name
This will create the table and at the same time it will copy all data from source table to destination table, if you are using other database for destination table then use the complete notation, like servername.databasename.owner.object name.
you can schedule this tsql as batch…
2. You can try DTS or SSIS package to do the same. It will create a table on the destination, only table no constraints, no indexes … nothing only table with all columns, data types remaining same in both the cases.
This task also creates a table and then inserts all the data from source to destination. you can schedule this package.
3. if you want to have an exact table in the destination with all the features of source table, then you might want to work like this,
create a batch file which will have all these things,
1. script the source table and then run the script on destination, where you want to create the table, this will create a table with the same name with all the features of the source table.
2. if you want to change the name, write another query,
sp_rename object ‘oldname’ , ‘newname’
3. Then final step, insert all the values from source to destination.
insert into new_table_name select * from old_table_name.
and schedule it … I hope it will work… I am not sure !
1. How to find out the table given a column, you only know that there is a table which has this column name, based on this information you can find out what table is it.
use database name
select table_name from information_schema.column where column_name = ‘column name’
2. If you know the table name and you want to know what is the name of the column, there are many ways I am sure, one amoung them is,
sp_help table_name … it will give alot of information… like owner of the table, all column name, their data type, primary keys , foriegn keys, indexes, constraint …. very useful information.
3. You dont know what was the name of the table and what was the name of the column and you still want to search table and column,
use database_name
select table_name , column_name from information_schema.column — copy this code as it is, dont try to change the table_name or column _name
How to find out user defined table names in the database,
use database_name
select * from sysobjects where type = ‘U’
Similarly you can find out other objects created by user, simple change type =
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = In-lined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table ( this is the one I discussed above in the example)
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Hi Pinal,
Please answer my questions?
1. When a database is restored to the original drive with the original filename, is the dbid changed?
2. When a database is restored to a different drive with the original filename, is the dbid changed?
Mani
Hi Pinal,
Here is the scenario:
On Saturday morning, I created a login named abcd using Enterprise Manager (EM).
In the database access tab, I gave abcd login access to databases x, y and z. I gave it db_datareader and db_datawriter roles in all three databases.
When I looked in the databases, I saw abcd user had been created in each database with all the correct privileges.
When I refreshed logins in EM, login abcd did not show that it had access to database z, but still had access to databases x and y.
None of this makes any sense. EM knew the correct database when I created the login and granted access, but did not show it after I refreshed.
When I did the same scenario on Monday morning, it worked properly and EM showed the correct database access.
I want to know what happens?why not in sat but in Monday?
Does SQL Server actually drop the database
when the database is restored?
Regards,
Mani
Each time I google advanced stuff for sql server I keep coming back here ; )
Thanks , thanks , thanks !!!
No one small idea partly made possible by this blog:
Use the description field to pass info for the GUI generator and get rid of all hardcoded names in GUI ; )
SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], value AS
[DESCRIPTION]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE class = 1 and t.name = ‘TableName_tb’
Could somebody tell me how to regex the [DESCRIPTION] = e.g. return only set of this one mathing the regex ?
some of the tables have 0 rows in my database. I want to run a query to list out all the tables with 0 rows. how do i do it?
i tried * from user_tables where num_rows is null
but this query retrieves wrong data.
Can anyone gimme the right query?
Use the method
select rows from sysindexes where object_name(id)=’table_name’ and indid<2
Also make sure you read this post
Hi
I want to find the perticular number from the column like
ex —11658458784
21565454797
21564567897
now i have to take 6 digit from the all number .
One method is
where len(col)=6
Hi Dave,
please reply back to my questions
a. How to get rows of a column with out the column name.
Thanks in anticipation
bye.
How to get the table structure in mssql similar to “DESCRIBE tablename” in MySQL.
EXEC sp_help table_name
how many tuples we can inserted in a relation
I have got here answer of my question that is “How can i count number of columns in a table”. Thanks Pinal.
HI,
I want to know the table structure using the quiery.
Plz send me the quiery to know abt the table structure
how can i retrive the number of columns in a table is not satisfied for me give me brief introduction
I found an alternative for ‘DESCRIBE tablename’ in mssql
“SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’myTable’;”
Actually similar equivalent is
EXEC sp_help ‘mytable’
I just want to read a specific row from a multi row return table…….i mean suppose……a query return this
date
——-
11/1/2007 3:19:28 PM
12/1/2007 3:19:28 PM
3/1/2008 3:19:28 PM
2/1/2008 3:19:28 PM
now…..how can i get an a specific row from above…..suppose i want to read each row but through numeric no…….if i say row no.3 or row no.2………..what should be the query……if anybody know plz help…….voidbutreturn@yahoo.com