Bir tablonun kolonlarını virgül ile ayrılmış olarak listelenmesini istiyorsanız alttaki sp'yi kullanabilirsiniz.
Sp'nin birinci parametresi tablo adı ikinci parametresi listeyi ne şekilde almak istediğinizi belirtebilirsiniz
Örnekler :
use AdventureWorks
exec us_col 'Person.Contact'
ContactID, NameStyle, Title, FirstName, MiddleName...
exec us_col 'Person.Contact',1
[ContactID], [NameStyle], [Title], [FirstName], [MiddleName]...
exec us_col 'Person.Contact',2
[ContactID] = rtrim(ltrim([ContactID])), [NameStyle] = rtrim(ltrim([NameStyle]))...
exec us_col 'Person.Contact',3 --Excel'den copy/past yapýnca gerekebiliyor
[ContactID] = ltrim(rtrim(replace ([ContactID],char(160),'')))...
exec us_col 'Person.Contact',4 --cursor ve insert'larda gerekebiliyor
@ContactID, @NameStyle, @Title, @FirstName, @MiddleName
exec us_col 'Person.Contact',5
@ContactID int, @NameStyle NameStyle, @Title nvarchar(8), @FirstName Name, @MiddleName Name
Sp'nin kodu:
create procedure us_col
@TableName varchar(100),
@t tinyint = null
as
/*
*/
if @t is null
SELECT STUFF((SELECT ', ' + name
FROM sys.columns where object_id =object_id(@TableName)
ORDER BY column_id
FOR XML PATH('')), 1, 1, '')
else if @t = 1
SELECT STUFF((SELECT ', [' + name + ']'
FROM sys.columns where object_id =object_id(@TableName)
ORDER BY column_id
FOR XML PATH('')), 1, 1, '')
else if @t = 2
SELECT STUFF((SELECT ', [' + name + '] = rtrim(ltrim([' + name + ']))'
FROM sys.columns where object_id =object_id(@TableName)
ORDER BY column_id
FOR XML PATH('')), 1, 1, '')
else if @t = 3
SELECT STUFF((SELECT ', [' + name + '] = ltrim(rtrim(replace ([' + name + '],char(160),'
+char(39) +char(39)+ ')))'
FROM sys.columns where object_id =object_id(@TableName)
ORDER BY column_id
FOR XML PATH('')), 1, 1, '')
else if @t = 4
SELECT STUFF((SELECT ', ' +'@'+ name
FROM sys.columns where object_id =object_id(@TableName)
ORDER BY column_id
FOR XML PATH('')), 1, 1, '')
else if @t = 5
SELECT STUFF((SELECT ', ' +'@'+ c.name + ' ' + t.name +
(case
when t.name in ('varchar', 'char')
then '('+ convert(varchar, c.max_length)+')'
when t.name in ('nvarchar', 'nchar')
then '('+ convert(varchar, c.max_length/2)+')'
else ''
end)
FROM sys.columns c
left join sys.types t on c.user_type_id = t.user_type_id
where c.object_id = object_id(@TableName)
ORDER BY c.column_id
FOR XML PATH('')), 1, 1, '')
--THE END
Subscribe