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 do you troubleshoot SQL Server if its running very slow?
Is sql server implemented as a service or an application? : Sql server database administration
What is an etl file?
What purpose does the model database server?
Which is better in performance - CONSTRAINT or TRIGGER over a column which restricts say an input of particular value in a column of a table?
can we give the identity for existing column of the table? (already table contains 10(1-10) unique randam records)
What is transaction server isolation?
What is the maximum number of index per table?
What are SQL Jobs, please mention the use of it and how to create them.
What are some of the pros and cons of not dropping the sql server builtinadministrators group? : sql server security
When would you use a before or after trigger?
Explain how does the report manager work in ssrs?