/**
查询表格的列,列类型,列长度
*/
CREATE PROC sp_columns1
@table_name VARCHAR(1000)
AS
BEGIN
SELECT a.name, c.data_type, a.length
FROM syscolumns a LEFT JOIN information_schema.columns c ON a.name = c.column_name AND c.TABLE_NAME = @table_name
WHERE a.ID = OBJECT_ID(@table_name)
END
/**
查询表的所有列,横向显示
*/
CREATE PROC sp_columns2(@table_name VARCHAR(1000))
AS
BEGIN
DECLARE @column_name VARCHAR(100) --列名
DECLARE @columns VARCHAR(1000) --所有列名
SELECT @columns = ''
DECLARE
mycursor CURSOR FOR
SELECT name
FROM syscolumns
WHERE id = (SELECT id
FROM sysobjects
WHERE name = @table_name)
OPEN mycursor
FETCH NEXT FROM MyCursor INTO @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @columns = @columns + @column_name + ','
FETCH NEXT FROM MyCursor INTO @column_name
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT @columns
END
分享到:
相关推荐
—先把数据导入到数据库 —其次: select * from sys.columns where object_id =(select object_id from sys.objects where name=’EMPLYEEs’) —方法2 —使用数据库提供的函数 exec sp_columns ‘Employees’ ...
Id int identity(1,1) not null primary key, --顾客编号,主键 按一进行自动增长 UserName varchar(50)not null, PassWord varchar(50)not null, Address varchar(250)not null, ) go ------------------------...
Inline EXEC function now unwraps sp_executesql statements (UserVoice) Option to align datatypes and constraints now applies to computed columns (Forum post) Fix for CTE not formatting (Forum post) ...
SP-6761 : Don't detect BP014 issue on computed non persisted columns. SP-6766 : BP014 Shouldn't apply to identity columns. SP-6777 : "Place expressions on new line" under THEN expressions moves ELSE ...
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明:四表联查问题: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner...
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明:四表联查问题: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c ...
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明:四表联查问题: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner...
2、添加DataGrid1控件:Ctrl+W打开类向导,工程->增加到工程->Components and Contols->Registered ActiveX Controls->Microsoft DataGrid Control 6.0 (sp6)(OLEDB)->Insert 3、为IDC_DATAGRID1关联变量m_DataGrid ...
Released 2/16/2017 SQL Prompt 7.4.1 Major features Support for SQL Server Management Studio 17 Formatting improvements: Improvements to begin/end formatting Improvements to nested parentheses Case...
SP-6819 : Fixed an issue in ALTER TABLE DROP where no columns or constraints would be suggested after selecting the first one. SP-6918 : Fixed a problem that could cause "Inline EXEC" refactoring to ...
SP-7138 : BP014 no longer reports an issue with columns that don't declare NULL/NOT NULL if that column is a primary key. SP-7206 : Qualify Object Names no longer tries to qualify column and index ...
可以结合sp_MSforeachdb再遍历所有用户数据库查看所有表的尺寸大小,注意它的参数@sql不能超过nvarchar(2000),这里就不贴出代码了。另外还可以定期运行并将结果保存下来,以便观察数据变化趋势。 查询单个数据库的...
DB (2) Data 1: sub-resource; Data 2: 0; Data 3: 0 File (3) Data 1: File ID; Data 2: 0; Data 3: 0 Index (4) Data 1: Object ID; Data 2: sub-resource; Data 3: Index ID Table (5) Data 1: Object ID; Data 2...
windows 8 1 windows 8 windows 7 windows vista with sp1 windows xp professional with sp2 or earlier service packs and microsoft windows 2000 with sp5 or earlier service packs these files are used to ...
Lesson 2: Concepts – Statistics 29 Lesson 3: Concepts – Query Optimization 37 Lesson 4: Information Collection and Analysis 61 Lesson 5: Formulating and Implementing Resolution 75 Module 6: ...
1 工资等级 数值型 2 1~99之间 2 工资标准 数值型 4 1~9999之间 5、 管理员注册表 字段 字段名 类型 宽度 说明 1 注册名 字符型 10 任意字符 2 口令 字符型 10 英文字母或数字 2.2.1数据结构 工资总=(ID,编号) ...
SP-6876 : Prevents doubling columns when expanding wildcards from overclause. SP-6923 : Preserves space before COLLATE inside CAST statement when using default style. See the full release notes for ...
The services window can be set so you can see all service names and all columns in the services window are fully visible. Just click "Standard" at the bottom and double click all the columns at the ...
//This table contains two columns:KeywordID int not null,KeywordName varchar(100) not null private string dataTableName = "Basic_Keyword_Test"; private string storedProcedureName = ...
Sp-5853: Fix for "Cannot find resource named 'System.Windows.Controls.StackPanel'. Resource names are case sensitive." Changing the styles folder path no longer requires a restart of SSMS Script as ...