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 |
Difference between LEN() and DATALENGTH() in sql server ?
What are statistics?
Why should we go for stored procedures? Why not direct queries?
What is the difference between DataRow.Delete() and DataRow.Remove()?
What are policy management terms?
You have a stored procedure, which execute a lengthy batch job. This stored procedure is called from a trigger you do not want to slow the data entry process you do not want trigger to wait for this batch job to finish before it completes itself what you can do to speed up the process?
How to delete exactly duplicate records from a table?
What is mean by candidate key?
What are temporal tables in sql server 2016?
How does a profiler work?
Can you pass expressions to function parameters?
Explain indexes disadvantages?