欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Sql server查詢表結構和查詢表在那些存儲過程中出現過

程序员文章站 2022-04-20 14:37:46
...

1、 查詢表的結構

select 
         [表名]=c.Name,         
         [列名]=a.Name,    
         [類型]=b.Name,
         [字節數]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G' 
                  when b.Name='xml' then '2^31-1字節/2G'
                  else rtrim(a.[max_length]) end,
         [長度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1'
                       else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,
         [小數]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
         [是否允許為空]=case when a.is_nullable=1 then '是' else '否' end,
         [列說明]=isnull(e.[value],''),
         [默認值]=isnull(d.text,'無')    
     from 
         sys.columns a
     left join
         sys.types b on a.user_type_id=b.user_type_id
     inner join
         sys.objects c on a.object_id=c.object_id and c.Type='U'
     left join
         syscomments d on a.default_object_id=d.ID
     left join
         sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 
     --left join
     --   sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1
     WHERE ((C.NAME = '表名'))--OR ISNULL('resda','') = '') 

2、查詢表在那些存儲過程中使用到 

SELECT DISTINCT o.name, o.xtype,o.*  
FROM syscomments c  
INNER JOIN sysobjects o ON c.id=o.id  
WHERE UPPER(c.TEXT) LIKE '%表名%'

3、跨庫創建一個相同的數據表

where 1<>2  --將數據一並copy到新表

where 1=2  --將表結構copy到新表,無數據。

SELECT  * INTO   [庫名].[dbo].[表名]  FROM [服務器名稱].[庫名].[dbo].[表名]  WHERE 1<>2

 

相关标签: Sql server