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 |
When would you prefer to have a minimum number of indexes?
What is inline table-value user-defined function?
what are the steps you will take to improve performance of a poor performing query? : Sql server database administration
What is attribute hierarchy? : sql server analysis services, ssas
CLR Integration ? what is Notification services ?
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
Can primary key be null?
Find first and last day of current month in sql server
write a query for list of owner who are having multiple bikes in below table 1 shanker pulsar 2 shanker Honda 3 shanker car 4 Balu pulsar 5 Balu Honda 6 Balu car 7 Shyam pulsar 8 Jaya Honda 9 Deepa car 10 vasu car
Can you use order by when defining a view?
Can we delete data from a view?
what are questions asked in TCS for database tester (sqlserver)for 2-3 exp?