--操作一条记录,操作所有记录
set rowcount 1 --操作一条记录
set rowcount 0 --操作所有记录
--查询表结构
select dbo.sysobjects.name as Table_name,
dbo.syscolumns.name as Column_name,
dbo.systypes.name as Type_name,
dbo.systypes.length as Type_length,
columnproperty(dbo.syscolumns.id,dbo.syscolumns.name,'precision') as Column_lengh,
case dbo.syscolumns.isnullable when 1 then 'Null' when 0 then 'Not Null' end as ColumnIsNull
from dbo.syscolumns inner join dbo.sysobjects
on dbo.syscolumns.id = dbo.sysobjects.id
left join dbo.systypes
on dbo.syscolumns.xtype = dbo.systypes.xusertype
where
dbo.sysobjects.name in ('BusinessUnit') and
--dbo.systypes.name = 'image' and
(dbo.sysobjects.xtype = 'u') and
(not (dbo.sysobjects.name like 'dtproperties'))
--查询所有表的行数,及总行数
create table #T
(
[name] nvarchar(255),
rows bigint
)
declare @Name nvarchar(255),@Sql nvarchar(4000)
declare TName CURSOR FOR
select [name] from sysobjects where xtype = 'U'
open TName
fetch NEXT from TName
into @Name
while @@FETCH_STATUS = 0
begin
set @Sql='INSERT INTO #T ([name],rows) SELECT '''+@name+''' AS name, COUNT(1) AS row FROM '+@name
exec(@Sql)
fetch NEXT from TName into @name
end
close TName
deallocate TName
select [name] as 表名 , rows as 行数 FROM #T
order by rows desc
select Sum(rows) as 总行数 from #T
drop table #T
--给分组列编序号
---------
--A 1--
--A 2--
--B 1--
--B 2--
--B 3--
--C 1--
---------
CAST(ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY [id]) AS int)
---------
--A 1--
--A 1--
--B 2--
--B 2--
--B 2--
--C 3--
---------
CAST(DENSE_RANK() OVER (ORDER BY [id]) AS int)
--按两个字段分组统计数量,再按第一个字段分组统计数量(避免子查询)
--使用COUNT(DISTINCT [Datetime])
SELECT [id], COUNT(DISTINCT [Datetime])
FROM #T
GROUP BY [id]
--动态拼接sql,传入传出参数
--语法:
sp_executesql
[@stmt = ] stmt[ {, [@params=] N'@parameter_name data_type [ [ OUT [ PUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
--用例:
declare @sql nvarchar(100)
declare @a int
declare @b int
--传出参数:
set @sql = 'set @x = 3'
exec sp_executesql @sql,N'@x as int output',@x = @b output
select @b
--传入参数
set @sql = 'select @x '
exec sp_executesql @sql,N'@x as int ',@x = 2
--传入传出参数
set @sql = ' set @y = (select @x) '
exec sp_executesql @sql,N'@x as int ,@y as int output',@x = @b,@y = @b output
select @b