数据库元数据查询与判断

数据库元数据查询与判断

获取数据库中所有的表

SELECT * FROM sys.tablesCode language: CSS (css)

获取所有表的字段

SELECT * FROM INFORMATION_SCHEMA.columnsCode language: CSS (css)

查找指定表的所有字段

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'User'Code language: JavaScript (javascript)

判断某个表里面有没有某个字段

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'User' AND COLUMN_NAME = 'Name'Code language: JavaScript (javascript)

判断某个数据库有没有某张表

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_1')
BEGIN
  -- 存在这张表就做一些操作
  DROP TABLE Table_1
END
GOCode language: JavaScript (javascript)

不存在某张表则创建

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_1')
BEGIN
  -- 不存在这张表就创建
  CREATE TABLE Table_1( id int)
END
GOCode language: JavaScript (javascript)

判断某个表里面有没有某个字段

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_1' AND COLUMN_NAME = 'id')
BEGIN
  -- 存在该字段就删除
  ALTER TABLE Table_1 DROP COLUMN id
END
GOCode language: JavaScript (javascript)

不存在某个字段则添加

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_1' AND COLUMN_NAME = 'age')
BEGIN
  -- 不存在该字段就添加
  ALTER TABLE Table_1 ADD age int
END
GOCode language: JavaScript (javascript)