数据库系统表操作汇总

--查询数据库中所有指定类型的字段名称和所在的表名
--eg: 下面查的是当前数据库中 所有字段类型为 nvarchar(max) 的字段名和表名
SELECT cols.object_id ,
cols.column_id ,
https://www.360docs.net/doc/e91609949.html, AS ColumnName ,
TYPE_NAME(cols.system_type_id) AS ColumnType ,
cols.max_length ,
https://www.360docs.net/doc/e91609949.html, AS TableName
FROM sys.columns cols
LEFT JOIN sys.objects AS obj ON cols.object_id = obj.object_id
WHERE TYPE_NAME(system_type_id) = 'nvarchar'
AND max_length = -1

--查询数据库中字段对应的表名
select name,OBJECT_NAME(id) as tablename from syscolumns where name='字段名'

--查询数据库中指定字段的数据类型
select column_name as 字段名,data_type as 数据类型 from information_schema.columns
where table_name = '表名' and column_name='字段名'

-- 查找数据库中包含 log 关键字的表
select * from sysobjects where xtype='U' and name like '%log%'