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 extended stored procedures?
Do you know what are different types of replication in sql server?
List the various tools available for performance tuning?
How do you size a resultset?
What is indexing a document?
What are the advantages of policy management?
How do clustered indexes store data?
Can you insert NULL in unique column?
How do you make a trace?
Do you know what are the restrictions that views have to follow?
Difference between Triggers and Stored Procedure
23 Answers Claimat, HCL, Protech, Silgate,
IF more than one Site is accessing the same Database server and I want to move the DB with Minimum down time? How will you do