how to find number of columns in a table in sql server 2000
and 2005 also
Answers were Sorted based on User's Feedback
Answer / kishore
select count(*) NoOfColumns from SYSCOLUMNS
WHERE id= (Select id from SYSOBJECTS where name = 'city')
In this querry may name is Tablename
Is This Answer Correct ? | 58 Yes | 23 No |
Answer / dharmendra k. dixit
@Anshul..
Bro..through your code u can obtain the Total numbers of
rows in your Table.
---------------------------------
For getting Total Columns i will use this:
SELECT Count(*)As Coloumns
FROM Sys.SysColumns
Where ID =
(SELECT ID FROM Sys.SysObjects Where Name = 'YourTableName')
Is This Answer Correct ? | 34 Yes | 8 No |
IN SQLSERVER 20005
SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TABLENAME'
try in sql server 2000
Is This Answer Correct ? | 29 Yes | 5 No |
Answer / bhaskar
select count(*) from syscolumns where id=(select id from
Sysobjects where [name]='Table_Name')
Is This Answer Correct ? | 13 Yes | 3 No |
Answer / anand k
--Count of Columns
SELECT COUNT(*) NO_OF_COLUMNS
FROM SYS.COLUMNS
WHERE OBJECT_ID = OBJECT_ID('<TableName>')
ORDER BY Column_ID
--Column Names
SELECT [NAME]
FROM SYS.COLUMNS
WHERE OBJECT_ID = OBJECT_ID('<TableName>')
ORDER BY Column_ID
Is This Answer Correct ? | 14 Yes | 6 No |
Answer / surbhi
SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS where
table_name='tablename'
Is This Answer Correct ? | 8 Yes | 1 No |
Answer / saravanan p
select count(column_Name) from information_schema.columns
where table_name='tableName'
Is This Answer Correct ? | 8 Yes | 4 No |
Answer / rajkumar v
select count(*) from information_schema.columns where
table_name='Emp'
EMP table name
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / mohan
None of the above answer can result the columns which is
having auto incremented (Identity).
Also if a column is binded to user defined data type that
is also excluded....
So the Correct Number of column cannot be resulted in the
above answers.........
Is This Answer Correct ? | 6 Yes | 4 No |
Answer / vidit tyagi
SELECT Count(*)As Coloumns
FROM SysColumns
Where ID =
(SELECT ID FROM SysObjects Where Name = 'TableName')
Is This Answer Correct ? | 6 Yes | 4 No |
What is a group function explain with an example?
What keyword you will use to get schema appended to the result set of a ‘for xml’ query?
Explain the microsoft sql server delete command? : SQL Server Architecture
What is use of dbcc commands?
As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
To which devices can a backup be created and where should these devices be located? : sql server management studio
Can we rollback records deleted by a truncate statement?
3 Answers CarrizalSoft Technologies, United Healthcare,
mention different types of relationships in the dbms?
How do you open a cluster administrator?
How to create function with parameter in sql server?
Is natural join and equi join same?
can you any body tell me simple recovery,full recovery,bulk logged recovery where can use?