Labels

Wednesday, May 4, 2011

How to get the first two columns of a table ?

/*
Just Assign the value for @TableName variable and change TOP () value to see first n no of columns from the select table.
*/
DECLARE @i INT ,
  @count INT,
  @TableName VARCHAR(50),
  @FieldList VARCHAR(200) ='',
  @SQL VARCHAR(200) = ''
 
SELECT @i =1,
  @TableName ='Accountant' -- Enter Tablename Here
SELECT TOP 2 sys.columns.Name INTO #Temp
FROM sys.objects INNER JOIN sys.columns 
ON sys.objects.object_id = sys.columns.object_id 
AND sys.objects.name =@TableName
DECLARE @FieldsName TABLE
    ( SrNo INT IDENTITY,
     FieldName VARCHAR(50)
    )
   
INSERT INTO @FieldsName (FieldName)
SELECT Name FROM #Temp
SELECT @Count = COUNT(*) from @FieldsName
WHILE(@Count >= @i)
 BEGIN
  SELECT @FieldList = @FieldList+FieldName+', ' FROM @FieldsName WHERE SrNo = @i
  SELECT @i = @i +1
 END
  SELECT @FieldList= Left(@FieldList,Len(@FieldList)-1)
  SELECT @SQL = 'Select ' +@FieldList +' From '+@TableName
   
EXEC  (@SQL)
DROP TABLE #temp
GO
-- OR --
declare @Columns nvarchar(2000)
declare @TableName nvarchar(100) = 'Accountant'
SELECT TOP 2 @Columns = COALESCE(@Columns, '') + sys.columns.Name + ','
FROM sys.objects INNER JOIN sys.columns 
ON sys.objects.object_id = sys.columns.object_id 
AND sys.objects.name =@TableName
Set @Columns = Left(@Columns,Len(@Columns)-1)
exec('select ' + @Columns + ' from ' +  @TableName)
GO
-- OR --
declare @Columns nvarchar(2000)
declare @TableName nvarchar(100) = 'Accountant'
SELECT TOP 2 @Columns = ISNULL(@Columns, '') + sys.columns.Name + ','
FROM sys.objects INNER JOIN sys.columns 
ON sys.objects.object_id = sys.columns.object_id 
AND sys.objects.name =@TableName
Set @Columns = Left(@Columns,Len(@Columns)-1)

No comments:

Post a Comment