sql知识点1

来源：互联网发布：php 共享内存加锁编辑：程序博客网时间：2024/06/11 16:22

--1.所有表的行数
SELECT a.id, b.[name], a.ROWS FROM sysindexes a, sys.tables b
WHERE a.id = b.[object_id] AND a.indid <=1
ORDER BY b.[name]
--2015-12-23 10:31:54

--2.用SQL语句计算出给定日期是星期几
select datename(weekday,'2014-07-23')
select datename(weekday,'2015-12-29')

--3.将当前数据库中所有表的smalldatetime 列改为nvarchar(20)

-- 将当前数据库中, 所有表的smalldatetime 列改为nvarchar(20)
-- 如果列上有索引/默认值之类的依赖项, 则无法修改
EXEC sp_msforeachtable
    @command1 = N'
DECLARE CUR CURSOR LOCAL
FOR
SELECT
    N''ALTER TABLE ? ALTER COLUMN ''
       + QUOTENAME(C.name)
       + N''nvarchar(20)''
FROM syscolumns C, systypes T
WHERE C.xusertype = T.xusertype
    AND T.name = ''smalldatetime''
    AND C.id = OBJECT_ID(N''?'')
OPEN CUR
DECLARE @s nvarchar(4000)
FETCH CUR INTO @s
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT(@s)
    EXEC(@s)
    FETCH CUR INTO @s
END
CLOSE CUR
DEALLOCATE CUR
',
    @whereand = N'
       AND EXISTS(
              SELECT * FROM syscolumns C, systypes T
              WHERE C.xusertype = T.xusertype
                  AND T.name = ''smalldatetime''
                  AND C.id = O.id)
'

-- 2016-01-14 09:52:19.120

0 0