本文主要是介绍查询sql servser的视图、函数和存储过程、以及表和列，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
SELECT NAME as VIEW_NAME, OBJECT_DEFINITION (id) as VIEW_DEFINITION,'none' as CHECK_OPTION, '0' as IS_UPDATABLE,* FROM sysobjects WHERE xtype='V'
SELECT NAME as VIEW_NAME, OBJECT_DEFINITION (object_id) as VIEW_DEFINITION,'none' as CHECK_OPTION
FROM sys.views where schema_id in (SELECT schema_id FROM sys.schemas where name='dbo') ;
SELECT NAME as pro_NAME, OBJECT_DEFINITION (id) as VIEW_DEFINITION,'none' as CHECK_OPTION, '0' as IS_UPDATABLE,* FROM sysobjects WHERE xtype='p'
SELECT NAME as pro_NAME, OBJECT_DEFINITION (object_id) as VIEW_DEFINITION,'none' as CHECK_OPTION
FROM sys.procedures where schema_id in (SELECT schema_id FROM sys.schemas where name='dbo') ;
select sysobjects.name as OBJECT_NAME ,OBJECT_DEFINITION (id) AS DEFINITION , '' as CommentString from sysobjects where xtype in('FN','IF','TF')
函数要传数据库和schema
SELECT ROUTINE_NAME AS OBJECT_NAME, ROUTINE_DEFINITION AS DEFINITION, '' as CommentString,*
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_CATALOG = 'master' and ROUTINE_SCHEMA ='dbo' ;
SELECT NAME as pro_NAME, OBJECT_DEFINITION (id) as VIEW_DEFINITION,'none' as CHECK_OPTION, '0' as IS_UPDATABLE,* FROM sysobjects WHERE xtype='p'
SELECT NAME as pro_NAME, OBJECT_DEFINITION (object_id) as VIEW_DEFINITION,'none' as CHECK_OPTION
FROM sys.tables where schema_id in (SELECT schema_id FROM sys.schemas where name='dbo') ;
select * from sys.objects where type='U';
select * from sys.tables
SELECT col.name FROM sys.columns col JOIN sys.objects obj ON col.object_id = obj.object_id WHERE obj.type = 'U' AND obj.name = 'orderDetails';
select * from sys.columns ;
CREATE PROCEDURE ssssss
AS
SET NOCOUNT ON;
SELECT OrderID
FROM orderDetails
WHERE OrderID IS NULL;
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
CREATE VIEW vOrders
AS
SELECT OrderID
FROM orderDetails
WHERE OrderID IS NULL;
这篇关于查询sql servser的视图、函数和存储过程、以及表和列的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
