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 / kumar.t
Select Count(Column_Name) As NoOfColumns From
Information_Schema.Columns Where table_Name='Company'
Is This Answer Correct ? | 22 Yes | 2 No |
Answer / jerry joseph
SELECT count(*) NoOfColumns FROM SYSCOLUMNS
WHERE id= (Select id from SYSOBJECTS where name = 'TableName')
Is This Answer Correct ? | 14 Yes | 6 No |
Answer / amit
SELECT count(*) NoOfColumns FROM SYSCOLUMNS
WHERE id= (Select id from SYSOBJECTS where name
= 'TableName')
Is This Answer Correct ? | 6 Yes | 2 No |
Answer / naren
sp_help tablename
another command is
select name from syscolumns where id=object_id
('table_name')
Is This Answer Correct ? | 6 Yes | 3 No |
Answer / sujitha
SELECT DISTINCT SYS.NAME,COUNT(*) FROM SYSOBJECTS SYS
INNER JOIN SYSCOLUMNS SYSCOL ON SYSCOL.ID=SYS.ID
WHERE SYS.XTYPE='U' GROUP BY SYS.NAME
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / anand k
--FOR Given Table Name.
SELECT COUNT(*) FROM SYS.COLUMNS
WHERE ID = OBJECT_ID('<TABLENAME>')
--For All UD Tables in the current DB
SELECT OBJ.NAME,COUNT(*)
FROM SYS.COLUMNS COL,SYS.OBJECTS OBJ
WHERE OBJ.OBJECT_iD = COL.OBJECT_ID
AND TYPE = 'U'
GROUP BY OBJ.NAME
Is This Answer Correct ? | 4 Yes | 4 No |
Answer / anuruddha
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON
C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
Is This Answer Correct ? | 3 Yes | 3 No |
HOW TO FIND THE EMPLOYEE DETAILS WHO ARE GETTING SAME SALARY IN EMP TABLE
What are the properties of the transactions?
What are four major operators that can be used to combine conditions on a where clause?
What is tempdb database? : SQL Server Architecture
Explain how long are locks retained within the repeatable_read and serializable isolation levels, during a read operation with row-level locking?
Where to find ntwdblib.dll version 2000.80.194.0?
How many nested transaction can possible in sql server?
what's the difference between a primary key and a unique key? : Sql server database administration
Can you link only other SQL Servers or any database servers such as Oracle?
Explain triggers in sql?
Tell me in brief how sql server enhances scalability of the database system?
Can you give me some DBCC command options?(Database consistency check) - DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.