sqlServer SQL

🧙 Questions

• db查询

☄️ Ideas

db查询

-- 查询所有databases
SELECT name FROM Master..SysDatabases
-- 查询当前databases下面的所有表
select name from sysobjects where xtype='u'
-- 查询表字段信息
select field,
       concat(concat(concat(type, '('), firstLen), iif(endLen = 0, ')', concat(concat(',', endLen), ')'))) as type
from (
         select syscolumns.name                                                    as field,
                systypes.name                                                      as type,
                COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'PRECISION')        as firstLen,
                isnull(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) as endLen
         from syscolumns
                  left join
              systypes on syscolumns.xusertype = systypes.xusertype
         where id = (select max(id) from sysobjects where xtype = 'u' and name = 'ispong_table')
     ) as t

SELECT 表名    = case when a.colorder = 1 then d.name else '' end,
       表说明   = case when a.colorder = 1 then isnull(f.value, '') else '' end,
       字段序号  = a.colorder,
       字段名   = a.name,
       标识    = case when COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 then '√' else '' end,
       主键    = case
                   when exists(SELECT 1
                               FROM sysobjects
                               where xtype = 'PK'
                                 and parent_obj = a.id
                                 and name in (
                                   SELECT name
                                   FROM sysindexes
                                   WHERE indid in (SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid)))
                       then '√'
                   else '' end,
       类型    = b.name,
       占用字节数 = a.length,
       长度    = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
       小数位数  = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
       允许空   = case when a.isnullable = 1 then '√' else '' end,
       默认值   = isnull(e.text, ''),
       字段说明  = isnull(g.[value], '')
FROM syscolumns a
         left join
     systypes b
     on
         a.xusertype = 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
         left join
     sys.extended_properties g
     on
         a.id = G.major_id and a.colid = g.minor_id
         left join
     sys.extended_properties f
     on
         d.id = f.major_id and f.minor_id = 0
where d.name = 'ispong_table'
order by a.id, a.colorder

创建表

默认建表全部下odb schema下面

use ispong_db;
-- create schema ispong_schema;

create table ispong_schema.users(
  username varchar(100),
  age      int
)

create table user2 ( id varchar(100),  age int)
drop table ispong_table

插入数据

INSERT INTO ispong_sqlserve.dbo.user1 (id, username) VALUES (N'1', N'ispong')
INSERT INTO ispong_sqlserve.dbo.user2 (id, age) VALUES (N'1', N'12')

创建视图

create view users
as
select user1.id, username, age
from user1
         left join user2 on user1.id = user2.id;

🔗 Links

• sqlserver website