![](https://img-blog.csdnimg.cn/20201014180756916.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
sqlserver
yueyang3
这个作者很懒,什么都没留下…
展开
-
查看表/视图结构
--SQLSERVERSELECT sysobjects.name AS 表名, syscolumns.name AS 列名, systypes.name AS 数据类型, syscolumns.length AS 数据长度 FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id I...原创 2014-06-05 13:21:47 · 966 阅读 · 0 评论 -
带输出的游标
[code="sql"]declare @count intdeclare @total intset @total=0declare @tablename varchar(100)declare @sql nvarchar(4000)declare mycursor cursor forselect name from sysobjects where name li...原创 2015-07-27 17:26:15 · 96 阅读 · 0 评论 -
查询指定表名的列类型及长度
[code="sql"]SELECT sysobjects.name AS 表名, syscolumns.name AS 列名, systypes.name AS 数据类型, syscolumns.length AS 数据长度 FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id ...原创 2014-10-14 10:04:42 · 119 阅读 · 0 评论 -
sqlserver中float转varchar,不想变成科学记数法
[code="sql"]select cast(cast(字段 as decimal(18,0)) as varchar)[/code]原创 2014-10-13 13:43:57 · 962 阅读 · 0 评论 -
带输出的游标写法
declare @count intdeclare @tablename varchar(100)declare @sql nvarchar(4000)declare mycursor cursor forselect name from sysobjects where name like 'test____'open mycursorfetch next from my...原创 2014-09-30 09:05:59 · 168 阅读 · 0 评论 -
转贴_SQL删除重复数据只保留一条
内容来源于:http://blog.csdn.net/anya/article/details/6407280用SQL语句,删除掉重复项只保留一条[code="sql"]在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断select * from peoplewhere pe...原创 2015-07-08 14:57:40 · 83 阅读 · 0 评论 -
修复SQLSERVER数据库用户
SELECT SUSER_SID ('test') --查询sid用户sp_addlogin 'roiland','test','master'goEXEC sp_grantdbaccess 'test', 'test'gosp_addrolemember 'db_owner','test'go2005重新安装数据库服务器上,备份还原的用户不可用的处理方...原创 2014-08-25 08:29:32 · 84 阅读 · 0 评论 -
建立跨服务器连接
[code="sql"]--sqlserver跨服务器查询EXEC sp_addlinkedserver@server='link',@srvproduct='',@provider='SQLOLEDB',@datasrc='192.168.1.120' --要连接的数据库服务器IPEXEC sp_addlinkedsrvlogin'link', --被访问...原创 2014-07-10 14:00:51 · 166 阅读 · 0 评论 -
修改数据库用户
[code="sql"]SELECT SUSER_SID ('test') --查询sid用户sp_addlogin 'test','test','master'gouse testdbEXEC sp_grantdbaccess 'test', 'test'gosp_addrolemember 'db_owner','test'go--2005重新安...原创 2014-06-10 13:31:54 · 85 阅读 · 0 评论 -
查询登陆名和用户(架构)的对应关系
--MSSQL 2005SELECT SP.name as [login_name],DP.default_schema_name as [Schema_with_user],DP.name as [user_name]FROM sys.database_principals DP ,sys.server_principals SPWHERE SP.sid = DP.sid--an...原创 2014-06-05 16:23:21 · 208 阅读 · 0 评论 -
根据表名查询详细信息
--适用于sqlserver2005,mysql,其他数据库未用过,不确定SELECT * from information_schema.columns WHERE table_name = '表名';原创 2014-06-05 13:34:56 · 241 阅读 · 0 评论 -
查询修改数据库恢复模式(SQLSERVER)
use master go alter database AdventureWorks set RECOVERY FULL--完全 go use master go alter database AdventureWorks set RECOVERY SIMPLE...原创 2014-06-05 13:26:45 · 900 阅读 · 0 评论 -
带输出的sqlserver游标(统计各表的数量)
[code="sql"]declare @count intdeclare @total intset @total=0declare @tablename varchar(100)declare @sql nvarchar(4000)declare mycursor cursor forselect name from sysobjects where name li...原创 2014-12-08 13:24:33 · 415 阅读 · 1 评论