The following script will create a SELECT statement for the table supplied as a parameter.
You'll need these two scripts also
f_4P_columnType
f_4P_columnTypeVB
-- =============================================
--
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_select'
AND type = 'P')
DROP PROCEDURE sp_select
GO
CREATE PROCEDURE sp_select
-- sp_select 'groups'
@vchrTableName varchar(50)
AS
set nocount on
declare @vchrProcName varchar(100)
declare @vchrFieldName varchar(100)
declare @vchrFieldType varchar(100)
declare @vchrFieldTypeVB varchar(100)
declare @vchrParamName varchar(100)
declare @intColstat int
declare @vchrParamList varchar(1000)
declare @vchrSelectList varchar(1000)
select
@vchrParamList = '',
@vchrSelectList = '',
-- @vchrParamListVB = '',
-- @vchrParamListHelper = '',
-- @output = '',
@vchrProcName = '_4P_' + upper(@vchrTableName) + '_SEL',
@vchrTableName = upper(@vchrTableName)
print 'IF EXISTS (SELECT name '
print ' FROM sysobjects '
print ' WHERE name = N' + char(39) + @vchrTableName + char(39)
print ' AND type = ' + char(39) + 'P' + char(39) + ')'
print ' DROP PROCEDURE ' + @vchrProcName
print 'GO'
PRINT ''
print 'CREATE PROCEDURE ' + @vchrProcName
print ''
declare curFields CURSOR for
select syscolumns.name,
dbo.f_4P_columnType (xusertype,length, xprec , xscale),
dbo.f_4P_columnTypeVB (xusertype),
<a href="mailto:'@'">'@'</a> + syscolumns.name,
syscolumns.colstat
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @vchrTableName
-- sp_select 'groups'
DECLARE @vchrWhere varchar(1000)
set @vchrWhere = ''
OPEN curFields
FETCH NEXT FROM curFields INTO @vchrfieldName, @vchrFieldType, @vchrFieldTypeVB,@vchrparamName, @intColstat
WHILE @@FETCH_STATUS = 0
BEGIN
--build a where clause
if @intColStat = 1 begin
set @vchrWhere = @vchrWhere + ' ' + @vchrFieldName + ' = ' + @vchrparamName+ char(13)
--build a param list (builds @name varchar(20) )
set @vchrParamList = @vchrParamList + char(13) + @vchrparamName + ' ' + @vchrFieldType + ','
end
--build the 'set' list
set @vchrSelectList = @vchrSelectList + char(13) + space(8) + @vchrFieldName + ','
FETCH NEXT FROM curFields INTO @vchrfieldName, @vchrFieldType,@vchrFieldTypeVB, @vchrparamName, @intColstat
END
CLOSE curFields
DEALLOCATE curFields
--remove the last char from the list
set @vchrParamList = left(@vchrParamList, len(@vchrParamList)-1)
--remove the first and last char from the list
set @vchrSelectList = stuff(@vchrSelectList,1,1,'')
set @vchrSelectList = left(@vchrSelectList, len(@vchrSelectList)-1)
print @vchrParamList
print ''
PRINT 'AS'
print ''
Print 'SELECT'
print @vchrSelectList
print ' FROM ' + @vchrTableName + ' WITH (NOLOCK)'
print ' WHERE'
print @vchrWhere
PRINT 'GO'
PRINT 'GRANT EXEC ON ' + @vchrProcName + ' TO PUBLIC'
-- sp_select 'groups'
go
grant exec on sp_select to public