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

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