Sql Server 数据库基础信息查看操作

SELECT  'INSERT INTO s_ODI_DBCollection(ODIGUID,ODIType,Code,Name) VALUES('''
        + CAST(ODIGUID AS VARCHAR(40)) + ''',''' + ODIType + ''',''' + Code
        + ''',''' + Name + ''');' sql_str
FROM    s_ODI_DBCollection;


--获取所有数据库名: 
SELECT  name
FROM    master..sysdatabases
ORDER BY name;

--获取所有字段名:
SELECT  name
FROM    MyTest..syscolumns
WHERE   id = OBJECT_ID('cb_Cost');

--获取一个表的所有字段信息
SELECT  syscolumns.name AS ColumnName ,
        systypes.name AS ColumnType ,
        CASE syscolumns.isnullable WHEN 1 THEN '√' ELSE '' END AS IsNullAble ,
        syscolumns.length AS Length
FROM    MyTest..syscolumns LEFT JOIN systypes ON syscolumns.xusertype = systypes.xusertype
WHERE   syscolumns.id = OBJECT_ID('cb_Cost');

EXEC sp_helptext @objname = 'usp_fy_interface_DelDeptCostUsedInfoForSH';


--获取存储过程及存储过程的内容
SELECT  sysobjects.id ,
        sysobjects.name ,
        syscomments.text
FROM    MyTest..sysobjects
        INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE   sysobjects.xtype = 'P'
ORDER BY sysobjects.name;

--获取所有视图
SELECT  sysobjects.id ,
        sysobjects.name ,
        syscomments.text
FROM    MyTest..sysobjects
        INNER JOIN syscomments ON syscomments.id = sysobjects.id
WHERE   xtype = 'V'
ORDER BY sysobjects.name;

--获取所有函数
SELECT  sysobjects.id ,
        sysobjects.name ,
        syscomments.text
FROM    MyTest..sysobjects
        INNER JOIN syscomments ON syscomments.id = sysobjects.id
WHERE   xtype = 'TF'
ORDER BY sysobjects.name;

--获取所有用户表
SELECT  sysobjects.id ,
        sysobjects.name
FROM    MyTest..sysobjects
WHERE   xtype = 'U'
ORDER BY sysobjects.name;

SELECT * FROM sysobjects
WHERE   xtype = 'U'

--获取所有数据库名: 
SELECT  *
FROM    master..sysdatabases
ORDER BY name;