Remove Table columns Dynamically in Sql
Sometimes we may require to remove columns from a table which has enormous columns. It's a cumbersome process if we do one by one. So here I have created a stored procedure. We would have to give table name and number of columns as input. CREATE PROC Usp_droptablecolumns ( @tablename VARCHAR(200), @ColumnNumber INT ) AS BEGIN DECLARE @Count INT --SET @tablename='temp' --SET @ColumnNumber=1 SELECT column_id,name INTO #test FROM SYS.COLUMNS WHERE OBJECT_ID =OBJECT_ID(@tablename) SET @Count=1 WHILE(@Count<=@ColumnNumber ) BEGIN DECLARE @ColumnName VARCHAR(200),@Query VARCHAR(500) SELECT @ColumnName=NAME FROM #test WHERE column_id=@Count SET @Query='ALTER TABLE '+ @tablename +' DROP COLUMN '+ @ColumnName --select @Query EXEC (@Query) SET @Count=@Count+1 END DROP TABLE #test END Execution sample Usp_droptablecolumns 'test',1