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
How can we find out the Column names in SQL Server
like the ORACLE command
DESC
which is use to show the table structure
thanks
—
To find a column name in a table..
Use ”
Go
Select Column_Name from Information_Schema.Columns
where Table_Name = ‘
Note ** you can also use a wildcard
Hopes this helps
Wole
dIRECT EQUIVALENT IS
SP_HELP 'TABLE_NAME'
how to list the columns of a particular table when table name is given as input??
There are many methods
Some of them are
EXEC sp_columns ‘table name’
SELECT column_name FROM information_schema.columns
WHERE table_name=’tabl name’
EXEC sp_help ‘table name’
Thanks alot for your script. It can be very useful.
Lalit
How to count indexes in particular table. i.e if we have 3 indexes in a particular table then how could we count in query. pls help
hey very nice! thanks Pinal! I am new to SQL and this came in very handy!!
HI Wole,
Thanks for your query finding column names in a tabel using Information_Schema.Columns…
i’m new to sql server…
Thanks & Regds
Rockin Benjamin.S
MS SQL query of showing all tables whose name starts with “P” in specefic database, use this query.
select * from sysobjects where name like ‘P%’
But It will show Constraints and tables,
if you want only user defined tables then use this query.
select * from sysobjects where name like ‘P%’ and xtype = ‘u’
Hi guys,
This is another easy way to find the max row count in a table.
select rowcnt from sysindexes where name in (
select name from sysobjects where parent_obj in
(
select id from sysobjects where name = ‘Table_Name’
)
hi wole
Thanks for helping in counting the number of columns in a table
thanks again
plz anyone send the query for how to count the number of columns in a table
How can we write to query for percentage of the marks?
Hi dave, Thanx for the info. I’m stuck with getting roWID from a select statement. I can’t use ROW_NUMBER() because i’m using 2000 instead or 2005
select count(*) as ‘Total Rows’ from tablename
this query will count number of rows in a table
Find the total rows in Table without the count(*)?
There are many methods like usage of SUM(1), COUNT(1) etc
Refer this for more methods
Madhivanan
how can i count number of column in a row with same data?
i have a table with column empid,month, day1, day2 ………….day31 i want to count number of ‘p’ and ‘a’ of that employee id?
select empid,count(*) as counting from employeetable
where empid in ('p','a')
group by empid
i think you did not get the scenario correctly, here the requirement is something like an attendance sheet, where an empid is marked present (p) or absent (a) based on the days and @Deepshika want the number of days present in that month.
take a table, table name : Attendance
_____________________________________________
| EmpID | Month | Day1| Day2| Day3|………………..|Day31|
————————————————————————
|001 | Jan | P | A | P |………………..| P |
|001 | Feb | A | P | p |……………….| |
|002 | Sep | P | P | P |………………..| |
|002 | Nov | A | A | P |………………..| |
|_____________________________________________
and so on,
so as per @Deepshika requirements is to get the count of no of days present (P) or absent (A) for the empid 001 in the month of Jan and same for all the months and empid’s. here we need to use UNPIVOT, to the count of columns value.
select EMPID, MONTH, DATA, Count(Data)
(select EMPID, MONTH, COL, DATA from
(select EMPID, MONTH, Day1, Day2, Day3,……………,Day31 from Attendance) as pvt UNPIVOT
(DATA for COL in (Day1, Day2, Day3,……………,Day31) as UPVT)) as pt group by EMPID, MONTH, DATA
i have given the sln i know, there may be many slns better than this one. if anyone know a better sln than provide that one here which will be helpful for other for developing the skills.
Column count in a table
create Procedure Prc_GetColumnCount
@TableName varchar(20)
as
Begin
Select Count(Name) from Syscolumns
Where
id =
(Select id from SysObjects Where name = @TableName)
End
Prc_GetColumnCount ‘Table name’
How do we extract all table list from SQL server?
I am using toad for SQL server and trying to find some field.
Can anyone pls tell me how to see the list of all the tables in SQL Server…………………
One of the methods is
EXEC sp_tables
Hey Awadh ….Here’s ur Answer..
select TABLE_NAME from INFORMATION_SCHEMA.[TABLES]
What is the maximum number of records that SQL will return for a query? e.g. I have a db of 15,000 records and I want to run a query which should return 13,000. Will i see all 13,000 or some fewer number?
Thanks
You will see all rows. But if you want to return billions of rows, it depends on the server’s RAM capacity
Hi Friends
You can refer
SELECT rows FROM sysindexes WHERE id = OBJECT_ID
(‘TableName’) AND indid < 2
Thanks