IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getAllTablesRows]’) AND type in (N’P’))
DROP procedure [dbo].[getAllTablesRows]
Go
create procedure getAllTablesRows
As
Begin
DECLARE @TableName sysname ,
@SQL nvarchar(max),
@SQLCol nvarchar(max),
@num int
IF OBJECT_ID(‘#Temp’) IS NOT NULL
drop table #Temp
create TABLE #Temp
( TableName nvarchar(max) NULL DEFAULT(”),
ColumnCount nvarchar(max) NULL DEFAULT(”),
CreatedBY nvarchar(max) NULL DEFAULT(”),
CreatedByName nvarchar(max) NULL DEFAULT(”),
CreatedOn nvarchar(max) NULL DEFAULT(”),
ModifiedBy nvarchar(max) NULL DEFAULT(”),
ModifiedOn nvarchar(max) NULL DEFAULT(”)
)
–set @SQL = ‘insert into #Temp ‘
DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
SELECT so.name from
sysobjects so
where so.xtype =’U’
order by so.name
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
select @num=count(TABLE_NAME) FROM information_schema.columns sc
where sc.COLUMN_NAME like ‘%ColumnName%’ and sc.TABLE_NAME = @TableName
group by TABLE_NAME
IF @num > 0
Begin
set @SQL = ‘ SELECT Top 5 ”’ + @TableName + ”’as TableName, ‘ + convert (nvarchar(10), @num ) + ‘ As ColumnCount ‘
set @SQLCol = ”
select @SQLCol = COALESCE(@SQLCol+’,’,”)+ sc.COLUMN_NAME FROM information_schema.columns sc
where sc.COLUMN_NAME like ‘%ColumnName%’ and sc.TABLE_NAME = @TableName
order by sc.COLUMN_NAME asc
if @num=1
Begin
set @SQLCol = @SQLCol + ‘,NULL,NULL,NULL,NULL’
End
else if @num=2
Begin
set @SQLCol = @SQLCol + ‘,NULL,NULL,NULL’
End
else if @num=3
Begin
set @SQLCol = @SQLCol + ‘,NULL,NULL’
End
else if @num=4
Begin
set @SQLCol = @SQLCol + ‘,NULL’
End
set @SQL = ‘insert into #Temp ‘+ @SQL + @SQLCol + ‘ FROM [‘ + @TableName + ‘] ‘
exec @SQL
set @num=0
End
FETCH NEXT FROM tables_cursor INTO @TableName
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
select * from #Temp
End
Welcome........ 