SQL_Select系统表查询04
--1、查询一个表中有多少个字段
select a.name AS 表名,count(0) 字段总数 from sys.objects a inner join sys.all_columns b on a.object_id=b.object_id where a.type='u' and a.name='bfCustomers'group by a.name
--2、查询某个字段是哪个表中的
select a.Name as tableName from sysobjects a inner join syscolumns b on a.ID=b.ID
where b.Name='字段名'
--3.1、根据数值查询出字段与表：比如在数据库中查询出包含“张三”的数据表和字段
declare @cloumns varchar(40)
declare @tablename varchar(40)
declare @str varchar(40)
declare @counts int
declare @sql nvarchar(2000)
declare MyCursor Cursor For
Select a.name as Columns, b.name as TableName from syscolumns a,sysobjects b,systypes c
where a.id = b.id
and b.type = 'U'
and a.xtype=c.xtype
and c.name like '%char%'
set @str='张三'
Open MyCursor
Fetch next From MyCursor Into @cloumns,@tablename
While(@@Fetch_Status = 0)
Begin
set @sql='select @tmp_counts=count(*) from ' +@tablename+ ' where ' +@cloumns+' = ''' +@str+ ''''
execute sp_executesql @sql,N'@tmp_counts int out',@counts out
if @counts>0
begin
print '表名为:'+@tablename+',字段名为'+@cloumns
end
Fetch next From MyCursor Into @cloumns,@tablename
End
Close MyCursor
Deallocate MyCursor
--3.2、根据数值查询出字段与表：比如在数据库中查询出包含“张三”的数据表和字段
CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
@value VARCHAR(1024)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)
DECLARE TABLES CURSOR FOR
SELECT o.name, c.name FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''')'
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @table, @column
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT * FROM #t
DROP TABLE #t
End
--只需要传入一个想要查找的值，即可查询出这个值所在的表和字段名。
exec [SP_FindValueInDB] '张三'
--3.3 查询所有的存储过程哪些中包含某个字符串
select name,* from sysobjects where id in (select id from syscomments where text like '%该%')
--4、查询出数据库中各个表的大小
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00)/1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY TotalSpaceMB
--5、查询触发器、存储过程中用到的表（这里查人事表）（函数或视图，也是改类型即可）
①select distinct object_name(id) from syscomments
where id in (select object_id from sys.objects where type ='tr')
and text like '%employeemsg%'
②select distinct object_name(id) from syscomments
where id in (select object_id from sys.objects where type ='P')
and text like '%employeemsg%'
--5.1、查询特定的表（字段或者文字）在哪些存储过程中被使用
SELECT DISTINCT
OBJECT_NAME(id)--,text
FROM syscomments
WHERE id IN ( SELECT id
FROM sysobjects
WHERE type IN ( 'V', 'P' ,'TF') ) --V表示视图，P表示存储过程，TF表示函数
AND (text LIKE '%FindText%')
--5.2、查询一个数据库中有多少表、存储过程、触发器、视图
SELECT * FROM sysobjects WHERE xtype = 'U' (P、TR、V)
--5.3、查询触发器所对应的表
select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],
triggers.is_instead_of_trigger AS [触发器类型],
case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'
when triggers.is_instead_of_trigger = 0 then 'AFTER'
else null
end as [触发器类型描述]
from sys.triggers triggers
inner join sys.tables tables on triggers.parent_id = tables.object_id
where triggers.type ='TR'
order by triggers.create_date
--6、查询出一个数据库的大小
EXEC sp_spaceused
EXEC sp_spaceused @updateusage = N'TRUE'
--7、查询出一张表的大小
EXEC sp_spaceused 'EmployeePhoto'
--8、查询电脑名
@@SERVERNAME
--9、查询出今日是否是周六或周日(7,1)
①数字形式：SELECT DATEPART(dw,GETDATE())1-7
②汉字形式：SELECT DATENAME(dw,GETDATE())日-周六
--10、
①月份间隔，判断12月一年入职
SELECT datediff(mm,'2018-09-10','2018-10'+'-01')+1>=13
②30天间隔，(30) 判断一个月30天入职
SELECT datediff(DAY,'2018-09-01','2018-10'+'-01')+1>=31
--11、时、分、秒
时:select datename(hh,getdate()) 分：select datename(mi,getdate()) 秒：select datename(ss,getdate())
--12.获取脚本的执行时间
declare @timediff datetime
select @timediff=getdate()
select * from dbo.EmployeeMsg
print '耗时:'+ convert(varchar(10),datediff(ms,@timediff,getdate()))
--13、查看表结构
sp_help vwdemployeecardmsg
--14、查看表结构创建语法
sp_helptext 'vwdemployeecardmsg'
--15、查询出客户端IP、客户端端口、服务器IP、服务器端口
SELECT client_net_address '客户端IP',client_tcp_port '客户端端口',local_net_address '服务器IP',local_tcp_port '服务器端口' FROM sys.dm_exec_connections
--16、在表批量添加字段，在人事表中批量添加字段做测试（添加80个字段）
DECLARE @I INT ,
@SQL NVARCHAR(1000)
SET @I=0;
WHILE (@I<=80)
BEGIN
SET @I=@I+1
SET @SQL ='ALTER TABLE PerEmployee ADD PerFields'+CONVERT(VARCHAR(10),@I)+' VARCHAR(50)'
EXEC
SP_EXECUTESQL @SQL;
END
--17、在表中批量删除字段
DECLARE @strSql NVARCHAR(4000);
DECLARE @strWhere NVARCHAR(1000);
DECLARE @TableName NVARCHAR(100);
SET @TableName='PerEmployee'
DECLARE @fieldName NVARCHAR(100);
DECLARE @strDelete NVARCHAR(100);
SET @strWhere = ' drop column '
SET @strDelete ='alter table '
--定义游标
DECLARE contact_cursor CURSOR FOR
--检索当前数据库中所有字段名称包含PerFields的字段
SELECT a.name AS TableName,b.name AS fieldName FROM sys.all_objects a
JOIN sys.all_columns b
ON a.object_id = b.object_id
WHERE a.type= 'U' AND b.name LIKE '%PerFields%'
ORDER BY a.Name
打开游标
OPEN contact_cursor
FETCH NEXT FROM contact_cursor
INTO @TableName,@fieldName
WHILE @@FETCH_STATUS = 0
BEGIN
--拼接SQL
SET @strSql = @strDelete+@TableName+@strWhere+@fieldName;
PRINT @strSql
EXECUTE sp_executesql @strSql
FETCH NEXT FROM contact_cursor
INTO @TableName,@fieldName
END
--关闭释放游标
CLOSE contact_cursor
DEALLOCATE contact_cursor
--查询字符中存在换行、空格的数据时需要去掉空格和字符
-- 方法1：使用自定义函数处理（如需重复使用）
-- 先创建函数
CREATE FUNCTION dbo.CleanWhiteSpace(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @result NVARCHAR(MAX)
SET @result = REPLACE(REPLACE(REPLACE(REPLACE(@input, ' ', ''), CHAR(9), ''), CHAR(13), ''), CHAR(10), '')
RETURN @result
END
查询：
select dbo.CleanWhiteSpace(字段) from tablename
浙公网安备 33010602011771号