发布于

批量删除MS SQL中的表


//批量删除MS SQL中的表
DECLARE @tablename VARCHAR(100)
DECLARE @sql VARCHAR(500)
DECLARE cur_delete_table CURSOR READ_ONLY FORWARD_ONLY FOR

SELECT name FROM sysobjects WHERE name LIKE 'BSTemp%' AND type='U'
OPEN cur_delete_table
FETCH NEXT FROM cur_delete_table INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @sql='DROP TABLE '+@tablename
  EXEC (@sql)
  FETCH NEXT FROM cur_delete_table INTO @tablename
END
CLOSE cur_delete_table
DEALLOCATE cur_delete_table

如果是视图，则type=’V’， 同时DROP TABLE改为DROP VIEW

发布于

ms sql动态获取指定表名的字段的字段类型


SELECT 
  (CASE WHEN a.colorder=1 THEN d.name ELSE '' END) N'表名',
  a.colorder N'字段序号',
  a.name N'字段名',
  (CASE WHEN COLUMNPROPERTY( a.id, a.name, 'IsIdentity')=1 THEN '√'ELSE '' END) N'标识',
  (CASE 
    WHEN (
      SELECT COUNT(*) FROM sysobjects WHERE name IN (
        SELECT name FROM sysindexes 
        WHERE id = a.id AND 
            indid IN (SELECT indid FROM sysindexkeys WHERE id = a.id AND colid in (SELECT colid FROM syscolumns WHERE id = a.id AND name = a.name)
        )
      ) AND xtype = 'PK')>0 
    THEN '√' 
    ELSE '' END
  ) N'主键',
  b.name N'类型',
  a.length N'占用字节数',
  COLUMNPROPERTY(a.id,a.name,'PRECISION') AS N'长度',
  ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS N'小数位数',
  (CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) N'允许空',
  ISNULL(e.text, '') N'默认值'
FROM syscolumns a 
LEFT JOIN systypes b  ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name<>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault=e.id
WHERE d.name = 'DOC_ORG' --要查询的表
ORDER BY object_name(a.id), a.colorder