比如示例数据库AdventureWorks中的表Production.ProductProductPhoto,Sales.StoreContact,Person.Address等等
通过语句select * from sys.tables只能获取到ProductProductPhoto,StoreContact,Address,我想知道它们上面的全称(隶属数据库名倒不要求一定有)
通过语句select * from sys.tables只能获取到ProductProductPhoto,StoreContact,Address,我想知道它们上面的全称(隶属数据库名倒不要求一定有)
select [name]
from sysobjects
where xtype='u'
from sys.tables as a
join sys.schemas as b
on a.schema_id=b.schema_id
where a.xtype ='U'
declare @outresult varchar(25)
exec my_table '表名', @outresult output
print @outresult
if exists(select 'x' from sysobjects where name='my_table' and type='p')
begin
drop procedure my_table
end
gocreate proc my_table
@cTable varchar(25),
@result varchar(25) output
as
set nocount onselect convert(varchar(14),Rtrim(LTrim(a.name)))+',',
Rtrim(LTrim(convert(varchar(10),Rtrim(LTrim(b.name)))))+
case b.name when 'numeric' then '('+convert(varchar(5),a.prec)+'+'+convert(varchar(5),a.scale)+')' else '('+convert(varchar(3),a.length)+')' end+',',
case a.isnullable when 0 then 'NOT NULL' else 'NULL' END
from syscolumns a, systypes b
where a.id=object_id(@cTable) and a.xtype=b.xtype
order by a.colorder
这样的行不?