total number of column name specified in table

Thank you for posting at Dotnetfunda
[Administrator]

DECLARE @intColumns	INT	,

		@varSQL		NVARCHAR(MAX)



SELECT	@intColumns = 1,

		@varSQL		= N'CREATE TABLE TB_TABLE1('	



WHILE(@intColumns <= 1025)

BEGIN

	SELECT @varSQL = @varSQL +  'Col' + CAST(@intColumns AS VARCHAR) + ' INT, '

	SELECT @intColumns = @intColumns + 1

END



SELECT @varSQL = @varSQL + ')'



EXEC(@varSQL)

/*

Msg 1702, Level 16, State 1, Line 1

CREATE TABLE failed because column 'Col1025' in table 'TB_TABLE1' exceeds the maximum of 1024 columns.*/

SELECT COUNT(1) 'No of Column(s)' FROM SYS.SYSCOLUMNS WHERE ID = OBJECT_ID('TableName')

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions