1 SELECT 'INSERT INTO s_ODI_DBCollection(ODIGUID,ODIType,Code,Name) VALUES('''
2 + CAST(ODIGUID AS VARCHAR(40)) + ''',''' + ODIType + ''',''' + Code
3 + ''',''' + Name + ''');' sql_str
4 FROM s_ODI_DBCollection;
5
6
7 --获取所有数据库名:
8 SELECT name
9 FROM master..sysdatabases
10 ORDER BY name;
11
12 --获取所有字段名:
13 SELECT name
14 FROM MyTest..syscolumns
15 WHERE id = OBJECT_ID('cb_Cost');
16
17 --获取一个表的所有字段信息
18 SELECT syscolumns.name AS ColumnName ,
19 systypes.name AS ColumnType ,
20 CASE syscolumns.isnullable WHEN 1 THEN '√' ELSE '' END AS IsNullAble ,
21 syscolumns.length AS Length
22 FROM MyTest..syscolumns LEFT JOIN systypes ON syscolumns.xusertype = systypes.xusertype
23 WHERE syscolumns.id = OBJECT_ID('cb_Cost');
24
25 EXEC sp_helptext @objname = 'usp_fy_interface_DelDeptCostUsedInfoForSH';
26
27
28 --获取存储过程及存储过程的内容
29 SELECT sysobjects.id ,
30 sysobjects.name ,
31 syscomments.text
32 FROM MyTest..sysobjects
33 INNER JOIN syscomments ON sysobjects.id = syscomments.id
34 WHERE sysobjects.xtype = 'P'
35 ORDER BY sysobjects.name;
36
37 --获取所有视图
38 SELECT sysobjects.id ,
39 sysobjects.name ,
40 syscomments.text
41 FROM MyTest..sysobjects
42 INNER JOIN syscomments ON syscomments.id = sysobjects.id
43 WHERE xtype = 'V'
44 ORDER BY sysobjects.name;
45
46 --获取所有函数
47 SELECT sysobjects.id ,
48 sysobjects.name ,
49 syscomments.text
50 FROM MyTest..sysobjects
51 INNER JOIN syscomments ON syscomments.id = sysobjects.id
52 WHERE xtype = 'TF'
53 ORDER BY sysobjects.name;
54
55 --获取所有用户表
56 SELECT sysobjects.id ,
57 sysobjects.name
58 FROM MyTest..sysobjects
59 WHERE xtype = 'U'
60 ORDER BY sysobjects.name;
61
62 SELECT * FROM sysobjects
63 WHERE xtype = 'U'
64
65 --获取所有数据库名:
66 SELECT *
67 FROM master..sysdatabases
68 ORDER BY name;
